Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vireshkolagimat
Contributor II

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

Re: values are loading as date

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;

22 Replies

Re: values are loading as date

What is your script you are using to create this?

vireshkolagimat
Contributor II

Re: values are loading as date

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

Re: values are loading as date

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

Re: values are loading as date

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
Contributor II

Re: values are loading as date

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

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

Re: values are loading as date

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
Contributor II

Re: values are loading as date

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

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;

Community Browser