Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vireshkolagimat
Creator III
Creator III

values are loading as date

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

22 Replies
vireshkolagimat
Creator III
Creator III
Author

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

sunny_talwar

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

vireshkolagimat
Creator III
Creator III
Author

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

vireshkolagimat
Creator III
Creator III
Author

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

sunny_talwar

Can you share a sample of your data to look at?

sunny_talwar

I have used @1 to grab the very 1st row from the Excel file (header row) and saved them in a concatenated list

Capture.PNG

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

,....

vireshkolagimat
Creator III
Creator III
Author

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.

sunny_talwar

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

vireshkolagimat
Creator III
Creator III
Author

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

sunny_talwar

You are renaming [UFU.A] to []? Not sure I understand, can you please provide more details here