topic values are loading as date in QlikView

values are loading as date

vireshkolagimat — Thu, 21 Apr 2016 09:53:20 GMT

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

Re: values are loading as date

sunny_talwar — Thu, 21 Apr 2016 15:09:13 GMT

What is your script you are using to create this?

Re: values are loading as date

vireshkolagimat — Wed, 27 Apr 2016 19:07:43 GMT

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

Re: values are loading as date

sunny_talwar — Wed, 27 Apr 2016 19:10:20 GMT

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;

Re: values are loading as date

Wed, 27 Apr 2016 22:44:11 GMT

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);

Re: values are loading as date

vireshkolagimat — Sun, 01 May 2016 09:52:30 GMT

thanks but how to handle if the dates changes every week. I mean how to handle the changes column header dates dynamically?

Re: values are loading as date

sunny_talwar — Sun, 01 May 2016 12:29:16 GMT

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;

Re: values are loading as date

Sun, 01 May 2016 12:40:50 GMT

Hi Viresh,

Can you please try below and let me know.

input:

LOAD

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

Re: values are loading as date

vireshkolagimat — Mon, 02 May 2016 11:48:16 GMT

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

Re: values are loading as date

sunny_talwar — Mon, 02 May 2016 13:02:38 GMT

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;

Re: values are loading as date

vireshkolagimat — Mon, 02 May 2016 14:26:05 GMT

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

Re: values are loading as date

sunny_talwar — Mon, 02 May 2016 14:28:13 GMT

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

Re: values are loading as date

vireshkolagimat — Wed, 04 May 2016 12:25:54 GMT

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

Re: values are loading as date

vireshkolagimat — Fri, 06 May 2016 16:06:31 GMT

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

Re: values are loading as date

sunny_talwar — Fri, 06 May 2016 16:11:20 GMT

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

Re: values are loading as date

sunny_talwar — Fri, 06 May 2016 20:06:51 GMT

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

,....

Re: values are loading as date

vireshkolagimat — Mon, 09 May 2016 19:22:54 GMT

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.

Re: values are loading as date

sunny_talwar — Mon, 09 May 2016 19:45:23 GMT

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,

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

Re: values are loading as date

vireshkolagimat — Mon, 27 Jun 2016 08:05:48 GMT

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

Re: values are loading as date

sunny_talwar — Thu, 30 Jun 2016 01:21:38 GMT

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