topic transform table in QlikView

transform table

qlikviewalex — Wed, 11 May 2016 12:23:57 GMT

Hello,

I have a table which needs to transform.

My table A looks like this:

LOAD

Key,

quantity,

Price

FROM....;

Key	quantity	Price
1	1	1.50
1	5	3.70
1	10	4.80
2	1	2.45
2	5	8.45
7	10	1.28
3	1	6.45
3	4	5.45
6	1	8.45
6	8	6.21
3	6	8.45

From this base, I need I table like this:

Key	quantity1	Price1	quantity2	Price2	quantity3	Price3
1	1	1.50	5	3.70	10	4.80
2	1	2.45	5	8.45
3	1	6.45	4	5.45	6	8.45
6	1	8.45	8	6.21
7	10	1.28

Have someone a solution for this problem?

I have an idea, but I don't know how to implement.

I sort my table. First the quantity.

Key	quantity	Price
1	1	1,50
2	1	2,45
3	1	6,45
6	1	8,45
3	4	5,45
1	5	3,70
2	5	8,45
3	6	8,45
6	8	6,21
1	10	4,80
7	10	1,25

In the second the Key

Key	quantity	Price
1	1	1,50
1	5	3,70
1	10	4,80
2	1	2,45
2	5	8,45
3	1	6,45
3	4	5,45
3	6	8,45
6	1	8,45
6	8	6,21
7	10	1,25

Now I need a new colum.

Key	quantity	Price	numberOfKey	=
1	1	1,50	=if( key(from row) = key(from row -1), then numberOfKey(from row-1) + 1, else 1 )	1
1	5	3,70	...	2
1	10	4,80		3
2	1	2,45		1
2	5	8,45		2
3	1	6,45		1
3	4	5,45		2
3	6	8,45		3
6	1	8,45		1
6	8	6,21		2
7	10	1,25		1

If I get a table like this I can transform these.

newA:

LOAD

Key,

quantity1,

Price1

FROM A

where numberOfKey = 1;

outer join (newA)

LOAD

Key,

quantity2,

Price2

FROM A

where numberOfKey = 2;

....

I hope somebody can help me.

Greetings

Alex

Re: transform table

settu_periasamy — Wed, 11 May 2016 18:13:48 GMT

You Almost got the solution. Just i created script based on your steps..

T1:
LOAD * INLINE [
Key, quantity, Price
1, 1, 1.5
1, 5, 3.7
1, 10, 4.8
2, 1, 2.45
2, 5, 8.45
3, 4, 5.45
3, 1, 6.45
3, 6, 8.45
6, 8, 6.21
6, 1, 8.45
7, 10, 1.28
];

NoConcatenate

T2:
LOAD *,if(Key=Previous(Key) and RowNo()<>1,Peek('Key1')+1,1) as Key1 Resident T1 Order by Key,quantity asc;

DROP Table T1;

NoConcatenate

newA:
LOAD Key,quantity as quantity1,Price as Price1 Resident T2 Where Key1=1;

Outer Join(newA)

LOAD Key,quantity as quantity2,Price as Price2 Resident T2 Where Key1=2;

Outer Join(newA)

LOAD Key,quantity as quantity3,Price as Price3 Resident T2 Where Key1=3;

DROP Table T2;

Note: Even we can simplify this using for loop (as i know)

Re: transform table

MarcoWedel — Wed, 11 May 2016 23:52:40 GMT

Hi,

a solution without hard coded field names could use a generic load:

The Generic Load

hope this helps

regards

Marco

Re: transform table

rwunderlich — Thu, 12 May 2016 00:16:52 GMT

The Generic Load script would be pretty simple in this case:

Data:

GENERIC LOAD

Key,

'Quantity' & AutoNumber(RecNo(), Key),

quantity

FROM

[https://community.qlik.com/thread/216434]

(html, codepage is 1252, embedded labels, table is @1);

Data:

GENERIC LOAD

Key,

'Price' & AutoNumber(RecNo(), Key),

Price

FROM

[https://community.qlik.com/thread/216434]

(html, codepage is 1252, embedded labels, table is @1);

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: transform table

qlikviewalex — Thu, 12 May 2016 07:00:28 GMT

Thanks for the solution! It works well!

regards

Alex