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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

22 Replies
sunny_talwar

What is your script you are using to create this?

vireshkolagimat
Creator III
Creator III
Author

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:

date.PNG

I want the header as dates in source data file.

thanks

Viresh

sunny_talwar

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;

Not applicable

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

vireshkolagimat
Creator III
Creator III
Author

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

sunny_talwar

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;

Not applicable

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

vireshkolagimat
Creator III
Creator III
Author

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

sunny_talwar

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;