Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi team,
I have below data and after loading into qlikview, the values are loading as dates like the header column.
Kindly help me to resolve this. and also how to keep the date as header.
prod 1/1/2016 5/1/2016 change
A 200 300 100
B 50 100 50
C 500 300 200
thanks
Viresh
Try this:
Table:
LOAD Concat(PurgeChar(Alt(Date(Num#(@1), 'DD-MMM'), @1), '#'), '|', Order) as List;
LOAD @1,
RecNo() as Order
FROM
Dynamically Renaming Fields in the Script.xlsx
(ooxml, explicit labels, table is Sheet1, filters(
Transpose()
));
LET vList = Chr(39) & Peek('List') & Chr(39);
DROP Table Table;
FinalTable:
LOAD *
FROM
Dynamically Renaming Fields in the Script.xlsx
(ooxml, no labels, header is 1 lines, table is Sheet1);
For i = 1 to NoOfFields('FinalTable')
LET vFieldOld = '[' & FieldName($(i), 'FinalTable') & ']';
LET vFieldNew = '[' & SubField($(vList), '|', $(i)) & ']';
RENAME Field $(vFieldOld) to $(vFieldNew);
NEXT i;
What is your script you are using to create this?
THis is the script i am using in editor:
data:
LOAD Products,
[42447],
[407680]
FROM
sample.xlsx
(ooxml, embedded labels, table is Sheet3);
and this is the out put:
I want the header as dates in source data file.
thanks
Viresh
Try doing this (with CrossTable load):
data:
CrossTable (Date, Value)
LOAD Products,
[42447],
[40768]
FROM
sample.xlsx
(ooxml, embedded labels, table is Sheet3);
FinalTable:
NoConcatenate
LOAD Products,
Date(Num#(Date, '##')) as Date,
Value
Resident data;
DROP Table data;
Load prod, change,
num([42370]) as [1/1/2016],
num([42491]) as [5/1/2016];
LOAD prod,
[42370],
[42491],
change
FROM
[New Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Sheet1);
thanks but how to handle if the dates changes every week. I mean how to handle the changes column header dates dynamically?
I think you should try using the CrossTable with LOAD *
data:
CrossTable (Date, Value)
LOAD *
FROM
sample.xlsx
(ooxml, embedded labels, table is Sheet3);
FinalTable:
NoConcatenate
LOAD Products,
Date(Num#(Date, '##')) as Date,
Value
Resident data;
DROP Table data;
Hi Viresh,
Can you please try below and let me know.
input:
LOAD
A,
B,
C,
D
FROM
(ooxml, no labels, table is Sheet1);
Let col1=Peek('A',0,'input');
Let col2=Peek('B', 0,'input');
Let col3=Peek('C', 0,'input');
Let col4=Peek('D', 0,'input');
NoConcatenate
output:
LOAD $(col1),
$(col2) as b ,
$(col3),
$(col4)
FROM
(ooxml, embedded labels, table is Sheet1);
Thanks,
Sreeman
Hi, Here is the example.
I have below data. I receive the data every week so on a weekly basis, the header is going to change in the source file. Currently it is showing as 26-mar and 20-Mar. When i receive the new data, the header will change to new data like 30-Mar and 24-Mar. In this case if i execute the load script, it will fail due to the missing previous headers. So how to deal with in this case.
I really appreciate your help.
Data, 26-Mar, 20-Mar
ABC,3,5
PQR,5,8
LMN,6,8
Regards,
Viresh
Try this:
Table:
LOAD Concat(PurgeChar(Alt(Date(Num#(@1), 'DD-MMM'), @1), '#'), '|', Order) as List;
LOAD @1,
RecNo() as Order
FROM
Dynamically Renaming Fields in the Script.xlsx
(ooxml, explicit labels, table is Sheet1, filters(
Transpose()
));
LET vList = Chr(39) & Peek('List') & Chr(39);
DROP Table Table;
FinalTable:
LOAD *
FROM
Dynamically Renaming Fields in the Script.xlsx
(ooxml, no labels, header is 1 lines, table is Sheet1);
For i = 1 to NoOfFields('FinalTable')
LET vFieldOld = '[' & FieldName($(i), 'FinalTable') & ']';
LET vFieldNew = '[' & SubField($(vList), '|', $(i)) & ']';
RENAME Field $(vFieldOld) to $(vFieldNew);
NEXT i;