topic DQL Matching and comparing on two arrays in DQL

DQL Matching and comparing on two arrays

calfano — Mon, 28 Oct 2024 20:17:24 GMT

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 🙂

Re: DQL Matching and comparing on two arrays

GerardJ — Tue, 29 Oct 2024 08:24:01 GMT

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[]))))

Re: DQL Matching and comparing on two arrays

calfano — Tue, 29 Oct 2024 12:48:24 GMT

WOW That's it! Thank you so much. This is very impressive!