CODEWITHSUNDEEP
qlikviewqliksensesenseset-analysisqlik-expression
mediii
mediii

Reputation: 97

Nested Set Analysis in QlikSense

My User_ID has several images and with this piece of code I receive the largest IMAGE_NR . 

  max({$<USER_ID = {'8638087'}> } IMAGE_NR)

Every image number is linked to an IMAGE_ID. How do I get this?

In words:

Give me IMAGE_ID where IMAGE_NR = largest IMAGE_NR of my USER_ID

I tried following that doesn't work:

  sum({$<max({<USER_ID={'8638087'}> } IMAGE_NR)>} IMAGE_ID)

Thank you for any thoughts!

Upvotes: 0

Views: 1210

Answers (3)

George Menoutis
George Menoutis

Reputation: 7260

Here is a step-by-step solution.

You mention that this already brings back the correct IMAGE_NR:

max({$<USER_ID = {'8638087'}> } IMAGE_NR)

Now you want the IMAGE_ID. The logic is:

=only({CORRECT_IMAGE_NR_SET_ANALYSIS} IMAGE_ID)

I generally prefer to avoid search mode (double quotes inside element list) and instead use a higher evaluation level on the top calculation, so I would recommend:

$(=
    'only({<IMAGE_NR={'
    & max({$<USER_ID = {'8638087'}> } IMAGE_NR)
    & '}>} IMAGE_ID)'
)

This would also provide you a nice preview formula in the formula editor, something like:

only({<IMAGE_NR={210391287}>} IMAGE_ID)

Upvotes: 2

The Budac
The Budac

Reputation: 1633

If the dimension of the table is USER_ID then this will return the IMAGE_ID of the maximum IMAGE_NR per USER_ID. Should work / return results for any dimension, but then will have to be read as maximum IMAGE_NR per whatever that dimesion is. The minus on IMAGE_NR is to get the largest/last sorted value

firstsortedvalue(IMAGE_ID,-IMAGE_NR)

If you're using it in a text box with no dimension then you can also add the set analysis 

firstsortedvalue({<USER_ID={'8638087'}>} IMAGE_ID,-IMAGE_NR)

Upvotes: 1

Idriss Benbassou
Idriss Benbassou

Reputation: 1606

I didn't test it, but i believe it's something like :

only({<IMAGE_NR={'$(=max(IMAGE_NR))'}, USER_ID={'8638087'}>} IMAGE_ID)

Upvotes: 1

Related Questions