Kusto query language joins not working

Question

Kusto query language joins not working

Kiwitech Developer 30

below query was working finer for hardcode

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

**but below query was not working for dynamic **
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
** UserAssessmentData table having  schema like this  **
 .create 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)

Sander van de Velde | MVP 36,951 Reputation points MVP Volunteer Moderator

2023-10-09T05:40:09.7233333+00:00

What is the question?
Kiwitech Developer 30 Reputation points

2023-10-09T09:17:59.8733333+00:00

@Sander van de Velde | MVP i have given two query . first query working with hardcoded table and data . but if i remove data and table which hardcoded am getting response as null
Sander van de Velde | MVP 36,951 Reputation points MVP Volunteer Moderator

2023-10-09T11:19:43.45+00:00

Thanks for the clarification.

Check out the answer given by @-- .

If this helps, please respond to that answer by marking it as the right answer.

Answer accepted by question author

0 additional answers

Your answer

Sander van de Velde | MVP 36,951 Reputation points MVP Volunteer Moderator

2023-10-09T05:40:09.7233333+00:00

What is the question?
Kiwitech Developer 30 Reputation points

2023-10-09T09:17:59.8733333+00:00

@Sander van de Velde | MVP i have given two query . first query working with hardcoded table and data . but if i remove data and table which hardcoded am getting response as null
Sander van de Velde | MVP 36,951 Reputation points MVP Volunteer Moderator

2023-10-09T11:19:43.45+00:00

Thanks for the clarification.

Check out the answer given by @-- .

If this helps, please respond to that answer by marking it as the right answer.

Answer 1

Wilko van de Velde 2,241

Hi @Kiwitech Developer ,

When I created the table and ingested the records, the second query worked fine. But there are some points you should pay attention to:

The column order in the table is different than in your parameter table
Check the data in your table, maybe you inserted spaces into the string columns. You may use the trim operator to check: https://learn.microsoft.com/nl-nl/azure/data-explorer/kusto/query/trimfunction

If this not helps, please post your query for creating the table with the data

Kind regards,
Wilko

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

Kiwitech Developer 30 Reputation points

2023-10-09T09:19:14.5833333+00:00

@-- Hi

what was the column order u talking about?
Kiwitech Developer 30 Reputation points

2023-10-09T09:30:59.36+00:00

@Wilko van de Velde this is my data in table
Wilko van de Velde 2,241 Reputation points

2023-10-09T09:40:31.3466667+00:00
Looking at your table screenshot the column order isn't the problem, because there is data in there.

I think there might be an issue with the data. Try running the query step by step to figure out where the issue is. For example start with:

UserAssessmentData | extend ['date'] = format_datetime(todatetime(timeStamp), 'yyyy-MM-dd') | where user_id == '87def01c-c48e-4096-8e9a-4feb25a78135'

Then

UserAssessmentData | extend ['date'] = format_datetime(todatetime(timeStamp), 'yyyy-MM-dd') | where user_id == '87def01c-c48e-4096-8e9a-4feb25a78135' | where assessmentCycle == 'IA'

And so on...
Kiwitech Developer 30 Reputation points

2023-10-09T11:06:15.0666667+00:00

@-- here showing empty results even am trying without user_id and date

also below image was working with hardcode values
Wilko van de Velde 2,241 Reputation points

2023-10-09T11:22:31.7033333+00:00
We can only guess because we dont have access to the data of the table. Did you check for empty spaces in the columns? For example:

let substring = " "; UserAssessmentData | extend ['date'] = format_datetime(todatetime(timeStamp), 'yyyy-MM-dd') | where user_id == '87def01c-c48e-4096-8e9a-4feb25a78135' | where assessmentCycle == trim(substring,'IA')

Or provide us with the content of the table by exporting the data:
Kiwitech Developer 30 Reputation points

2023-10-09T11:35:51.5+00:00
@-- this query i checked let substring = " "; UserAssessmentData | extend ['date'] = format_datetime(todatetime(timeStamp), 'yyyy-MM-dd') | where user_id == '87def01c-c48e-4096-8e9a-4feb25a78135' | where assessmentCycle == trim(substring,'IA') same issue .

below was the csv data for user_id =

87def01c-c48e-4096-8e9a-4feb25a78135

can u tell me how can i add csv here ? am not able to uplaod here . below was one of the row values

"record_id": 11wdxvf2vza4runwmvo11wdxikgx59n9, "user_id": 87def01c-c48e-4096-8e9a-4feb25a78135, "assessmentCycle": IA, "organization_id": fb9b72e7-76b8-41f7-a5e7-963495f9de25, "form_id": gyg7t6yu, "user_pool_id": d4c366dc-1f10-47de-962a-e2477bf8dcb3, "category": OKS, "measure_name": AEfti12soRc1, "timeStamp": 2023-10-07 T12:21:11.4479401Z, "measure_value": 3, "measure_value_varchar": null
Wilko van de Velde 2,241 Reputation points

2023-10-09T11:43:58.3333333+00:00

I dont know how to upload a csv. But looking at the yaml you provided, it looks like the following record have a empty space in front (I highlighted the empty space at the category):
Kiwitech Developer 30 Reputation points

2023-10-09T11:56:00.3733333+00:00

@-- okay shall i made query based on this space . can u help me with query? else i need to change the insert logic?

Wilko van de Velde 2,241

I think this should work:

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 substring = " ";
let JoinedData = 
    UserAssessmentData
    | extend ['date'] = format_datetime(todatetime(timeStamp), 'yyyy-MM-dd')
    | where user_id == trim(substring,'87def01c-c48e-4096-8e9a-4feb25a78135')
    | where assessmentCycle == trim(substring,'IA')
    | extend namewithoutspaces = trim(substring,measure_name)
    | join kind=inner (measure_name_map) on $left.namewithoutspaces == $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

But it will be better to change the insert logic, so you have a clean dataset.

Kiwitech Developer 30 Reputation points

2023-10-09T12:54:07.68+00:00

@-- thanks for ur help . i changed insert logic . now it was working fine . once again thank u so much

Share via

Kusto query language joins not working

0 additional answers

Your answer