topic Load date from excel and do cross join in qlikview in QlikView

Load date from excel and do cross join in qlikview

udaya_kumar — Mon, 26 Oct 2015 12:03:43 GMT

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.

Re: Load date from excel and do cross join in qlikview

Gysbert_Wassenaar — Mon, 26 Oct 2015 12:31:02 GMT

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;

Re: Load date from excel and do cross join in qlikview

maxgro — Mon, 26 Oct 2015 12:37:07 GMT

CrossTable(Month, Data, 3)

LOAD

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet1);

NoConcatenate LOAD

ID,

Name,

Department,

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

Data

Resident T;

DROP Table T;

Re: Load date from excel and do cross join in qlikview

udaya_kumar — Mon, 26 Oct 2015 12:41:10 GMT

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.

Re: Load date from excel and do cross join in qlikview

Gysbert_Wassenaar — Mon, 26 Oct 2015 12:45:36 GMT

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;

Re: Load date from excel and do cross join in qlikview

udaya_kumar — Mon, 26 Oct 2015 12:45:44 GMT

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.

Re: Load date from excel and do cross join in qlikview

maxgro — Mon, 26 Oct 2015 12:52:02 GMT

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

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;