Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expressions using values of different rows from same datatable

Hi all

my datatable looks like this

SKUPrimarySKUAvailabilityVolume
AA530
BA720
CA9100
DD310
EE15

I am trying to create a pivot chart showing

SKUAvailabilityVolumePrimarySKUAvail. PrimarySKUVolumeOccupiedExp VolOccupied
A

5

30A530*530*5
B720A520*720*5
C9100A5100*9100*5
D310D310*310*3
E15E15*15*1

Basically the SKU field is leading and for some of these, there is a common primary SKU

I would like to have some expressions that are calculations of fields directly related to SKU field (like VolumeOccupied, easy)
I also would like to calculate based on values linked to PrimarySKU field, like Avail. PrimarySKU. This is more difficult for me.

Or even a mixture of the two, so values linked to SKU together with values linked to PrimarySKUm as in Exp VolOccupied.

Any idea how to realise last two expressions?

thank you!

3 Replies
demoustier
Creator
Creator

Hi,

a solution in attached file with ApplyMap function

Kind regards

Not applicable
Author

thank you for your input.

indeed this one is a workable solution.

But I wouldn't like to change the script; and since I need to apply this to many, many fields, I would like to have some sort of set analysis to easily use values from different fields.

JonnyPoole
Employee
Employee

Here is something that works with this small data set

For the 1st calc , i used the  total <PrimarySKU> to evaluate the subset of recrods for each unique Primary SKU (3 for PrimarySKU=A etc...)  and spit out the record where SKU=PrimarySKU.  If there are multiple records, just add the 'distinct' keyword after the "only(" part:

only( total <PrimarySKU>  if(SKU=PrimarySKU,Availability))

Capture.PNG.png