Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a question, I have a table in DB with many field that have this name:
Table1:
ItemID01
ItemID02
ItemID03
...
Quantity01
Quantity02
Quantity03
...
I need to load every Item field anf quantity with the same field name.
I try with this:
For i=1 to 3
Temp:
LOAD 'ItemID' + text(num(i,'00')) AS ItemID.
'Quantity' + text(num(i,'00')) AS Quantity
FROM Table1;
but seem that QlikView dont resolve names of field 'ItemIDxx and Quantityxx.
SO I dont know wich is the right syntax.
Please can you help me?
Thank you very much.
Table1:
LOAD * INLINE [
IDKEY, ItemID01, ItemID02, ItemID03, Quantity01, Quantity02, Quantity03
1, A, B, C, 5, 10, 8
2, E, A, D, 8, 1, 9
];
final_table:
LOAD IDKEY,
ItemID01 as ItemID,
Quantity01 as Quantity
Resident Table1;
concatenate ('final_table')LOAD IDKEY,
ItemID02 as ItemID,
Quantity02 as Quantity
Resident Table1;
concatenate ('final_table') LOAD IDKEY,
ItemID03 as ItemID,
Quantity03 as Quantity
Resident Table1;
drop Table Table1;
Hi,
now it work!
For i=1 to 3
LET vItem = 'ItemID' & text(num(i,'00'));
LET vQuantity = 'Quantity' & text(num(i,'00'));
Temp:
LOAD $(vItem) AS ItemID.
$(vQuantity') AS Quantity
FROM Table1;
Itm_Temp:
CrossTable(ItemID,ItemValue,1)
Load IDKEY,ItemID01,ItemID02,ItemID03 Inline [
IDKEY,ItemID01,ItemID02,ItemID03,Quantity01,Quantity02,Quantity03
1, A, B,C,5,10,8
2, E, A,D,8,1,9 ];
Itm_Final:
Load *,PurgeChar(ItemID,'ItemID') As Ord Resident Itm_Temp;
Qty_Temp:
CrossTable(Qty,QtyValue,1)
Load IDKEY,Quantity01,Quantity02,Quantity03 Inline [
IDKEY,ItemID01,ItemID02,ItemID03,Quantity01,Quantity02,Quantity03
1, A, B,C,5,10,8
2, E, A,D,8,1,9 ];
Join(Itm_Final)
Load *,PurgeChar(Qty,'Quantity') As Ord Resident Qty_Temp;
Drop Tables Itm_Temp,Qty_Temp;
Hi,
Try like below,
Hopefully your issue will resolve,
A:
load * inline [
ItemID01, ItemID02, ItemID03
1, 2, 3
];
Let vMax = right('ItemID03',2);
Let vMin = right('ItemID01',2);
for i='$(vMin)' to '$(vMax)'
Let vi = text(num($(i),'00'));
B:
Load
ItemID$(vi) as ItemID
resident A;
NEXT
drop table A;