QlikView difference between columns

[kdavorin]

Hello.

I am newbie to QlikView and would greatly appreciate any help. Because of my very busy schedule I am unable to dive deeper into the QlikView topics at the moment, so any suggestion would be of great value!

I have the following data structure:

Scenario	KPI	Value
A	K1	10
A	K2	20
A	K3	30
B	K1	5
B	K2	13
B	K3	27

How could I achieve the following report?

KPI	A	B	Difference (A-B)
K1	10	5	5
K2	20	13	7
K3	30	27	3

Thank you very much for your time and effort.

[Taoufiq_Zarra · Accepted solution]

@kdavorin  if you need a sample solution in chart for 2 (A and B)

in dimension: KPI

measures1(A): =Only({<Scenario={'A'}>}Value)

measures B(B): =Only({<Scenario={'B'}>}Value)

measure(A-B): =Only({<Scenario={'A'}>}Value)-Only({<Scenario={'B'}>}Value)

output:

or other solution more global in Script using generic load :

Tmp:
 load KPI,Scenario,  Value INLINE [
    Scenario, KPI, Value
    A, K1, 10
    A, K2, 20
    A, K3, 30
    B, K1, 5
    B, K2, 13
    B, K3, 27
];

DATA:
 Generic LOAD * Resident Tmp;
 
CombinedGenericTable:

Load distinct KPI resident Tmp;

Drop Table   Tmp; 

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'DATA.*') THEN 

  LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF 

NEXT i

In chart: dimension KPI, A and B

measure A-B

output:

[kdavorin]

@Taoufiq_Zarra Thank you very much! That simple solution works perfect! 🙂

How do I achieve the same output, if my data structure is the following.

Scenario	KPI	SubKPI1	SubKPI2	Value
A	K1	-	S2a	3
A	K1	-	S2b	7
A	K2	-	S2a	11
A	K2	-	S2b	9
A	K3	S1a	S2c	4
A	K3	S1a	S2d	7
A	K3	S1b	S2c	10
A	K3	S1b	S2d	9
B	K1	-	S2a	2
B	K1	-	S2b	3
B	K2	-	S2a	6
B	K2	-	S2b	7
B	K3	S1a	S2c	2
B	K3	S1a	S2d	8
B	K3	S1b	S2c	11
B	K3	S1b	S2d	6

Thank you once again!

[Taoufiq_Zarra]

@kdavorin  and the what is the expected output ?

the first or the second solution ?

[kdavorin]

@Taoufiq_Zarra The expected output is the from the simple (first) solution. 

[Taoufiq_Zarra]

@kdavorin  what is the expected output from this input :

Scenario	KPI	SubKPI1	SubKPI2	Value
A	K1	-	S2a	3
A	K1	-	S2b	7
A	K2	-	S2a	11
A	K2	-	S2b	9
A	K3	S1a	S2c	4
A	K3	S1a	S2d	7
A	K3	S1b	S2c	10
A	K3	S1b	S2d	9
B	K1	-	S2a	2
B	K1	-	S2b	3
B	K2	-	S2a	6
B	K2	-	S2b	7
B	K3	S1a	S2c	2
B	K3	S1a	S2d	8
B	K3	S1b	S2c	11
B	K3	S1b	S2d	6

[kdavorin]

@Taoufiq_Zarra The expected output is the same.

KPI	A	B	Difference (A-B)
K1	10	5	5
K2	20	13	7
K3	30	27	3

It's just that the input data structure now contains some sub KPI's.

[Taoufiq_Zarra]

@kdavorin  just change Only to sum

in dimension KPI

measures1(A): =sum({<Scenario={'A'}>}Value)

measures B(B): =sum({<Scenario={'B'}>}Value)

measure(A-B): =sum({<Scenario={'A'}>}Value)-sum({<Scenario={'B'}>}Value)

output:

[kdavorin]

@Taoufiq_Zarra Thank you! Works like a charm! 😀