cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

DQL Matching and comparing on two arrays

calfano
Helper

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 🙂

2 REPLIES 2

GerardJ
Advisor

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

 

GerardJ_0-1730189113409.png

 

Gerard

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

Featured Posts