topic Re: Pivot Table Question in QlikView

Pivot Table Question

Sat, 21 Jun 2014 04:41:45 GMT

Hi All,

I have a question regarding using pivot table. I have a table here:

	Month	Jan 2014(A)	Feb 2014(B)	Mar 2014(C)	Mar 2014(D)	Apr 2014(E)	May 2014(F)	Jun 2014(G)
Desc	Letter	A	B	C	D	E	F	G
Valid		100	150	200	250	300	400	500

As you can see, on the table there are 2 Mar 2014. for C and D. I only want to show the latest one, which is (D).

How to determine latest? It's just base on alphabetical order (A<B<C<D<E..etc)

So my question is, if the system detects a common month between the letters it should only show the latest letter. It should not show the previous.

P.S this is not the actual table, this is just an example so I can get an idea.

Thanks.

Re: Pivot Table Question

Anonymous — Sat, 21 Jun 2014 05:41:04 GMT

Hi,

Something like below,

LastValue:

LOAD

Month&'('&Desc&')' as Month,

Desc,

Valid;

LOAD

SubField(Month, '(',1) as Month,

LastValue(Desc) as Desc,

LastValue(Valid) as Valid

Group By SubField(Month, '(',1);

LOAD * INLINE [

Month, Desc, Valid

Jan 2014(A), A, 100

Feb 2014(B), B, 150

Mar 2014(C), C, 200

Mar 2014(D), D, 250

Apr 2014(E), E, 300

May 2014(F), F, 400

Jun 2014(G), G, 500

];

PS: You may have to use Order By clause in your actual table to align data in order.

Re: Pivot Table Question

Sat, 21 Jun 2014 05:47:52 GMT

Hi thanks for the reply. Please do note that I still need to output letter C. It doesn't mean I have to remove it. It still there if I selected it.

Re: Pivot Table Question

Anonymous — Sat, 21 Jun 2014 08:54:20 GMT

How about creating last value flag by using mapping load. See below,

LastValue:

Mapping

LOAD

Month&'('&Desc&')' as Month,

1 as LastValueFlag;

LOAD

SubField(Month, '(',1) as Month,

LastValue(Desc) as Desc

Group By SubField(Month, '(',1);

LOAD * INLINE [

Month, Desc, Valid

Jan 2014(A), A, 100

Feb 2014(B), B, 150

Mar 2014(C), C, 200

Mar 2014(D), D, 250

Apr 2014(E), E, 300

May 2014(F), F, 400

Jun 2014(G), G, 500

];

FactTable:

LOAD Month,

Desc,

Valid,

ApplyMap('LastValue', Month, 0) as LastValueFlag

INLINE [

Month, Desc, Valid

Jan 2014(A), A, 100

Feb 2014(B), B, 150

Mar 2014(C), C, 200

Mar 2014(D), D, 250

Apr 2014(E), E, 300

May 2014(F), F, 400

Jun 2014(G), G, 500

];

And in the pivot table expression, use below script.

Sum ({$<LastValueFlag={1}>} Valid)

Hope it helps you.

Satya