Join not working for dynamic

[naveenkumar-kiwi]

below was the query working for hardcoded values .

let measure_name_map = datatable(name:string, display_name:string)
[
    'deN7YhOsB088', 'stsNumberofReps',
    'CwKX4G7p378K', 'tugTimeToComplete',
    'qaEAgA172sLI', 'checkedIn',
    'tuJ3vxHjAv8S', 'clinicalSummary',
    'dGWkYkvpx7Rg', 'sessionsAttended',
    'K9rGLIzEnfPK', 'treatmentSummary',
    'qv6Kc3uaFzim', 'treatmentSummaryOptions'
];
let UserAssessmentData = datatable(
    user_id:string,
    timeStamp:datetime,
    assessmentCycle:string,
    measure_name:string,
    measure_value:real,
    measure_value_varchar:string
)
[
    '87def01c-c48e-4096-8e9a-4feb25a78135', datetime(2023-10-09), 'IA', 'deN7YhOsB088', 10.5, '10.5',
    '87def01c-c48e-4096-8e9a-4feb25a78135', datetime(2023-10-08), 'IA', 'CwKX4G7p378K', 18.3, '15.3',
    '87def01c-c48e-4096-8e9a-4feb25a78135', datetime(2023-10-07), 'IA', 'qaEAgA172sLI', 1.0, '1.0',
    '87def01c-c48e-4096-8e9a-4feb25a78135', datetime(2023-10-09), 'IA', 'deN7YhOsB081', 10.5, '10.5',
    '87def01c-c48e-4096-8e9a-4feb25a78135', datetime(2023-10-08), 'IA', 'CwKX4G7p371K', 15.3, '15.3',
    '87def01c-c48e-4096-8e9a-4feb25a78135', datetime(2023-10-07), 'IA', 'qaEAgA1721LI', 1.0, '1.0'
];
let JoinedData = 
    UserAssessmentData
    | extend ['date'] = format_datetime(timeStamp, 'yyyy-MM-dd')
    | where user_id == '87def01c-c48e-4096-8e9a-4feb25a78135'
    | where assessmentCycle == 'IA'
    | join kind=inner (measure_name_map) on $left.measure_name == $right.name;
JoinedData
| project ['date'], assessmentCycle, element_id = iff(isnotempty(display_name), display_name, measure_name), measureName = iff(isnotempty(display_name), display_name, measure_name), measureValue = toint(measure_value), measureValueChar = tostring(measure_value_varchar)
| order by ['date'] desc 

above query was working fine . same if am used dynamic way am not getting results

 let measure_name_map = datatable(name:string, display_name:string)
[
    'deN7YhOsB088', 'stsNumberofReps',
    'CwKX4G7p378K', 'tugTimeToComplete',
    'qaEAgA172sLI', 'checkedIn',
    'tuJ3vxHjAv8S', 'clinicalSummary',
    'dGWkYkvpx7Rg', 'sessionsAttended',
    'K9rGLIzEnfPK', 'treatmentSummary',
    'qv6Kc3uaFzim', 'treatmentSummaryOptions'
];
let JoinedData = 
    UserAssessmentData
    | extend ['date'] = format_datetime(todatetime(timeStamp), 'yyyy-MM-dd')
    | where user_id == '87def01c-c48e-4096-8e9a-4feb25a78135'
    | where assessmentCycle == 'IA'
    | join kind=inner (measure_name_map) on $left.measure_name == $right.name;
JoinedData
| project ['date'], assessmentCycle, element_id = iff(isnotempty(display_name), display_name, measure_name), measureName = iff(isnotempty(display_name), display_name, measure_name), measureValue = toint(measure_value), measureValueChar = tostring(measure_value_varchar)
| order by ['date'] desc

this query was not working . what was the difference . for UserAssessmentData table am having below table UserAssessmentData (record_id: string, user_id: string, assessmentCycle: string, organization_id: string, form_id: string, user_pool_id: string, category: string, measure_name: string, timeStamp: string, measure_value: real, measure_value_varchar: string)

[sloutsky]

The one difference I can spot is that UserAssessmentData has column of timeStamp defined as a string. Depending on the format of the string - transformation of it to todatetime() may not be successful. Check if you are not getting 'null' values from "| extend ['date'] = format_datetime(todatetime(timeStamp), 'yyyy-MM-dd')"

[naveenkumar-kiwi]

@sloutsky okay i am having empty space issue in my records . after updating insert logic now am able to get the results . thanks