Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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
Contributor

Re: Expressions using values of different rows from same datatable

Hi,

a solution in attached file with ApplyMap function

Kind regards

Not applicable

Re: Expressions using values of different rows from same datatable

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.

Employee
Employee

Re: Expressions using values of different rows from same datatable

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

Community Browser