topic Re: rows into column in QlikView

rows into column

EDehzn — Thu, 28 Apr 2022 07:02:29 GMT

Hi All,

I have been trying to transform rows of my data into a new column to mimick SQL outer join. Example as below:

current data:

Customer	MIA	Value	item amount
A	1	200	12
B	1	325	10
C	1	450	5
A	2	12	2
C	2	700	32

desired outcome:

Customer	MIA	Value	item amount	MIA	Value	item amount
A	1	200	12	2	12	2
B	1	325	10
C	1	450	5	2	700	32

I am currently preferred to have the data as granular as possible, thus i am wondering if this transformation is possible to be executed using straight table expressions?

Re: rows into column

edwin — Mon, 02 May 2022 18:45:07 GMT

two possible options:

1. use a pivot table and use MIA as the pivoted dimension - simplest i think

2. continue using a straight table:
- column 2create measure use =only({<MIA={1}>}MIA) or just =1
- columns 3 and 4 add in your set analysis {<MIA={1}>}, this will compute only for MIA =1
- column 5 create measure use =only({<MIA={2}>}MIA) or just =2
- columns 6 and 7 add in your set analysis {<MIA={2}>}, this will compute only for MIA =2

so for column 3, if your original measure is
=sum(Value)
it becomes
=sum({<MIA={1}>}Value)

Re: rows into column

Saravanan_Desingh — Tue, 03 May 2022 03:03:15 GMT

Try this,

tab1: LOAD * INLINE [ Customer, MIA, Value, item amount A, 1, 200, 12 B, 1, 325, 10 C, 1, 450, 5 A, 2, 12, 2 C, 2, 700, 32 ]; tab2: Generic LOAD Customer, 'MIA'&MIA, MIA Resident tab1; Generic LOAD Customer, 'Value'&MIA, Value Resident tab1; Generic LOAD Customer, 'item amount'&MIA, [item amount] Resident tab1; Drop Table tab1;