topic Re: QlikView difference between columns in QlikView

QlikView difference between columns

kdavorin — Thu, 19 Aug 2021 07:19:21 GMT

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.

Re: QlikView difference between columns

Taoufiq_Zarra — Thu, 19 Aug 2021 08:50:55 GMT

@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:

Re: QlikView difference between columns

kdavorin — Thu, 19 Aug 2021 09:16:07 GMT

@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!

Re: QlikView difference between columns

Taoufiq_Zarra — Thu, 19 Aug 2021 09:28:26 GMT

@kdavorin and the what is the expected output ?

the first or the second solution ?

Re: QlikView difference between columns

kdavorin — Thu, 19 Aug 2021 09:31:22 GMT

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

Re: QlikView difference between columns

Taoufiq_Zarra — Thu, 19 Aug 2021 09:33:18 GMT

@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

Re: QlikView difference between columns

kdavorin — Thu, 19 Aug 2021 09:36:39 GMT

@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.

Re: QlikView difference between columns

Taoufiq_Zarra — Thu, 19 Aug 2021 09:41:06 GMT

@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:

Re: QlikView difference between columns

kdavorin — Thu, 19 Aug 2021 09:48:38 GMT

@Taoufiq_Zarra Thank you! Works like a charm! 😀