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
Hi,
I am getting the error saying, field @1 is not found. Are you using the same data what i have provided or something else. please let me know.
thanks for your valuable time.
Regards,
Viresh
Yes, but saved the data in the excel file. I have attached the Excel in my last response (Excel + qvw) Re: values are loading as date
Hi,
Thank you. It is working as expected..
what changes i have to make if i have additional fields in the source data. The test data i have provided has only 3 fields here.
these dates comes from multiple tables. Is there anyway i can create some script to handle this changes at one place.
and also, you used the order @1 and RecNo() as Order. Could you please explain me a bit.
Regards,
Viresh
Hi, I am getting the error after modifying the script as per my requirement. Is the first part (Till Drop table table) of the script is depending on second part.
Why you have used @1 and Order field which is not available in the source file.
Please let me know if i am doing something wrong.
thank you.
Regards,
Viresh
Can you share a sample of your data to look at?
I have used @1 to grab the very 1st row from the Excel file (header row) and saved them in a concatenated list
Order is used so that when I dynamically assign the field names their actual field title, I do it in the order of the load. If I don't use the order. It might flip flop the names based on ascending order of the text.
So a file such as this
Name, Country
Rahul, India
Josh, Sri Lanka
...
May end up looking like this
Country, Name
Rahul, India
Josh, Sri Lanka
,....
Hi,
Kindly find the attached file. I tried to run it and getting error saying that @1 is not found.
I have similar data which comes from different tables. How to manage all these dates.
Try this:
Table:
load Concat(@1,'|', Order) as List;
LOAD @1,
RecNo() as Order
FROM
demo.xlsx
(ooxml, explicit labels, table is Sheet1, filters(
Transpose()
));
LET vList = chr(39) & Peek('List') & chr(39);
drop table Table;
FinalTable:
LOAD A,
B,
C,
D,
E
FROM
demo.xlsx
(ooxml, explicit 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;
You might need to fix the date format, but no errors here
Hi, Hope you are doing good.
One question. I have multiple tables where the column names keep on changing and some fields have common names. so i used QUALIFY * and UNQUALIFY * word before and after the script for each table. But when i tried to execute the above code i am facing some errors. How to fix this?
Below is the error message for your reference:
Script line error:
RENAME Field [UFU.A] to []
UFU is the table name.
I really appreciate your help.
Regards,
Viresh
You are renaming [UFU.A] to []? Not sure I understand, can you please provide more details here