Pivot table question

[jduluc12]

Hi,

My data set looks like this:

Main:

ID	cust	Amount
1	Cust A	20
2	Cust B	30
3	Cust A	40
4	Cust B	50

Second:

ID	Typename
1	A
3	B
2	C

The chart should look like this:

Cust	Amount 1	Amount 2
Cust A	20 + 40
Cust B	30	50

So Amount 1 is sum of amounts for those IDs which are present in both Main and Second table and

Amount 2 is sum of amounts for those IDs which is present only in Main table.

For Cust A, both IDs 1 and 3 are present in both tables so the Amount 1 column has 20 + 40

For Cust B,  ID# 2 is present in both tables so Amount 1 has 30 and ID# 4 is present only in Main table so Amount 2 column has 50.

Jean

[Frank_Hartmann · Accepted solution]

Script:

Second:
Mapping LOAD ID, 'Cust '&Typename as cust
FROM [https://community.qlik.com/thread/305069]
(html, codepage is 1252, embedded labels, table is @2);


Main:
LOAD
    if(Applymap('Second',ID,'Null')='Null',1,0) as Flag,
    ID,
    cust,
    Amount
FROM [https://community.qlik.com/thread/305069]
(html, codepage is 1252, embedded labels, table is @1);

Straight/Pivot Table:

Dim = cust

Expression1:

sum({<Flag={0}>}Amount)

Expression2:

sum({<Flag={1}>}Amount)

OUTPUT: