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: 
skompel2
Contributor II
Contributor II

Pivot table: Merge Fields

I have a excel file like this:

ProdAgreed Capacity
E20
T30
C60
S93
L160 

I have another excel file with batch/lot sizes for similar prod groups:

ProdLotBatch
E (NA)1 
T 10
C (NA)1 
S 1
L 1
E 2
C 5

I created new field in script and collected all the NON - "NA "  products as "PILOT". To create a pivot table comparing capacity with qty, I used PILOT and AGREED CAPACITY  as Dimensions and in expression I used:

=if(PILOT='C',Sum([BATCH]),If(PILOT='S',Sum([BATCH]),If(PILOT='L',Sum([BATCH]),If(PILOT='E',Sum([BATCH]),If(PILOT='T',Sum([BATCH])))))). My ideal solution should be like this:

PilotAgreed CapacitySUM
E202
T3010
C605
S931
L1601

But I get the table as this:Capture.PNG

etc etc..... How can i match fields in one excel sheet field to the other? Please help.

Labels (2)
1 Reply
sunny_talwar

May be you need this

Sum(If(Pilot = Prod, Batch))