Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table which needs to transform.
My table A looks like this:
A:
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
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)
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)
Hi,
a solution without hard coded field names could use a generic load:
hope this helps
regards
Marco
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
Thanks for the solution! It works well!
regards
Alex