Load date from excel and do cross join in qlikview

[udaya_kumar]

Hi,

I have attached the sample data excel file and an image.

I want to load that excel file into qlikview and do crossjoin to load the date (Month) values as rows instead of columns.

When i try to load the excel file into QlikView, the date values shows like as attached in the image file.

How to load the date values properly into qlikview and do cross join in the script?

P.S : The dates will be getting added every month when new data appears.

[Gysbert_Wassenaar]

Like this:

MyTable:

CrossTable(Date,Value,3)

LOAD * FROM

[Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

The Date field contains numbers, which is what dates really are. You can format these values in charts and expressions using the Date() function: Date(Date, 'DD/MM/YYYY')

If you want to do it in the script then you need an additional load:

Result:

NoConcatenate

LOAD ID, Name, Department, Date(Date, 'DD/MM/YYYY')  as Date

Resident MyTable;

Drop Table MyTable;

[maxgro · Accepted solution]

T:

CrossTable(Month, Data, 3)

LOAD

  *

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet1);

F:

NoConcatenate LOAD

  ID,

    Name,

    Department,

    date(trim(Month),'MMM-YY') as Month,

    Data

Resident T;

DROP Table T;   

[udaya_kumar]

Hi Gysbert,

Thanks for you reply.

I tried the above code, but converting date to date format is not working.

It is showing null (0 rows) for date field.

[Gysbert_Wassenaar]

Hmm, possible the values are converted to text by the crosstable function. In which case you need to convert them to numbers first:

Result:

NoConcatenate

LOAD ID, Name, Department, Date(num#(Date), 'DD/MM/YYYY')  as Date

Resident MyTable;

[udaya_kumar]

Hi Maxgro,

Thanks for your reply.

It is working fine, but i have even quarter values in the date field like the attached image.

Please check it and help me.

[maxgro]

I think you can use something similar to check for Quarter or Month

F:

NoConcatenate LOAD

  ID,

    Name,

    Department,

    //Date(Month, 'MMM-YY') as Month,

    if(left(Month,1)='Q', Month) as Quarter,

    if(left(Month,1)<>'Q', date(trim(Month),'MMM-YY')) as Month,

    Data

Resident T;