28 Oct 2024 08:17 PM
Hello DQL Experts,
Running into a situation where I have two arrays. I want to be able to make records based on the comparison of these two arrays without using the expand or summarize commands.
I can't really articulate it well so here's an example of what I'm looking for.
array1 = ["cat", "cat", "dog", "dog", "dog", "snake"]
array2 = ["cat", "dog", "snake"]
Desired Output:
totals = { "cat":2, "dog":3, "snake":1 }
Any help is greatly appreciated 🙂
Solved! Go to Solution.
29 Oct 2024 08:06 AM - edited 29 Oct 2024 08:24 AM
Hello @calfano
Assuming that you want to count in array1 the number of occurence of values in array2, I'd do something like this :
data record()
| fieldsAdd array1 = Array("cat", "dog", "snake", "dog", "dog", "snake")
| fieldsAdd array2 = Array("cat", "dog", "snake", "cow")
| fieldsAdd array1 = arraySort(array1, direction:"ascending")
| fieldsAdd result = iCollectArray(concat(array2[],":",
if(arrayLastIndexOf(array1, array2[])>=0, arrayLastIndexOf(array1, array2[])-arrayIndexOf(array1,array2[])+1,
else:arrayLastIndexOf(array1, array2[]))))
29 Oct 2024 12:48 PM
WOW That's it! Thank you so much. This is very impressive!