# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
New Contributor III

## transform table

Hello,

I have a table which needs to transform.

My table A looks like this:

A:

Key,

quantity,

Price

FROM....;

KeyquantityPrice

1

11.50
153.70
1104.80

2

12.45
258.45
7101.28
316.45
345.45
618.45
686.21
36

8.45

From this base, I need I table like this:

Keyquantity1Price1quantity2Price2quantity3Price3
111.5053.70104.80
212.4558.45
316.4545.4568.45
618.4586.21
7101.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.

KeyquantityPrice
111,50
212,45
316,45
618,45
345,45
153,70
258,45
368,45
686,21
1104,80
7101,25

In the second the Key

KeyquantityPrice
111,50
153,70
1104,80
212,45
258,45
316,45
345,45
368,45
618,45
686,21
7101,25

Now I need a new colum.

KeyquantityPricenumberOfKey=
111,50

=if(

key(from row) = key(from row -1),

then

numberOfKey(from row-1) + 1,

else

1

)

1
153,70...2
1104,803
212,451
258,452
316,451
345,452
368,453
618,451
686,212
7101,251

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

newA:

Key,

quantity1,

Price1

FROM A

where numberOfKey = 1;

outer join (newA)

Key,

quantity2,

Price2

FROM A

where numberOfKey = 2;

....

I hope somebody can help me.

Greetings

Alex

Tags (2)
1 Solution

Accepted Solutions

## Re: transform table

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

T1:
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)

4 Replies

## Re: transform table

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

T1:
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)

MVP

## Re: transform table

Hi,

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

hope this helps

regards

Marco

MVP & Luminary

## Re: transform table

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

Data:

Key,

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

quantity

FROM

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

Data:

Key,

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

Price

FROM

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

-Rob