topic Re: Handling dates and cross table in QlikView

Handling dates and cross table

Fri, 20 Nov 2020 18:02:30 GMT

Hi there !

I am a new Qlik View user so please do not hesitate to be very exhaustive in your comments if you see what I mean 😄

I already looked for similar discussions but despite of all my efforts I couldn't manage to achieve what I am trying to do.

Here is the thing. I have an excel table which is - I assume - particularly inclined for a cross table. I let you figure out yourself :

Market Name	Jan-10	Feb-10	Mar-10	Apr-10
Market A	16.54%	20.02%	19.55%	27.54%
Market B	19.23%	44.47%	42.85%	35.38%
Market C	7.07%	6.82%	5.01%	5.15%
Market D	28.76%	33.76%	28.50%	53.39%
Market E	12.03%	11.08%	10.94%	9.99%

Just so you know, the dates go up to Dec 2015 and are going to be updated as the time goes on (meaning I will add soon columns for 2016 year).

As suggested in this brilliant post https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable#start=25 , I used a crosstable.

Since the dates were displayed as "numeric dates" when importing the excel file, here is my script :

tmpData:

CrossTable(MonthNumb,[CS_R+B])

LOAD [Market Name],

[40179],

[40210],

[40238],

[40269],

[40299],

[40330],

[40360],

[40391],

[40422],

[40452],

[40483],

[40513],

[40544],

[40575],

[40603],

[40634],

[40664],

[40695],

[40725],

[40756],

[40787],

[40817],

[40848],

[40878],

[40909],

[40940],

[40969],

[41000],

[41030],

[41061],

[41091],

[41122],

[41153],

[41183],

[41214],

[41244],

[41275],

[41306],

[41334],

[41365],

[41395],

[41426],

[41456],

[41487],

[41518],

[41548],

[41579],

[41609],

[41640],

[41671],

[41699],

[41730],

[41760],

[41791],

[41821],

[41852],

[41883],

[41913],

[41944],

[41974],

[42005],

[42036],

[42064],

[42095],

[42125],

[42156],

[42186],

[42217],

[42248],

[42278],

[42309],

[42339]

FROM

blablabla.xlsx

(ooxml, embedded labels, table is tab1);

// And here I would like to convert the dates to a more convenient display so I put this :

CS_calendar:

LOAD Date(Date#(MonthNumb,'DD-MM-YYYY'),'DD-MM-YYYY') as Dates,

[CS_R+B]

Resident tmpData;

// And here maybe I could write something like Drop tmpData;

But it does not work : the dates remain loaded as '40...' etc

My approach might be too naive, so I would really appreciate some help if someone feels like providing some.

Thank you very much, looking forward reading you.

Luc

Re: Handling dates and cross table

Gysbert_Wassenaar — Wed, 17 Jun 2015 16:21:13 GMT

The MonthNumb values are text after the CrossTable load. You need to turn the text values into numbers first:

Date(Num#(MonthNumb),'DD-MM-YYYY') as Dates

Re: Handling dates and cross table

Wed, 17 Jun 2015 16:37:30 GMT

This is perfect. Thank you so much.

Oh my. These are considered as text because I typed it manually, right ?

Could I ask for a little more sophisticated and ask for a more systematic way of loading these dates ? (something similar to LOAD *)

Thanks again

Re: Handling dates and cross table

sunny_talwar — Wed, 17 Jun 2015 16:43:30 GMT

I think you can use star, but the dates might still be pulled in as numbers, which you have to change back to date. I am sure this will work:

tmpData:

CrossTable(MonthNumb,[CS_R+B])

LOAD *

FROM

blablabla.xlsx

(ooxml, embedded labels, table is tab1);

// And here I would like to convert the dates to a more convenient display so I put this :

Data:

LOAD [Market Name],

Date(Num#(MonthNumb),'DD-MM-YYYY') as Dates,

[CS_R+B]

Resident tmpData;

DROP Table tmpData;

and you can add calendar table after this....

Re: Handling dates and cross table

Gysbert_Wassenaar — Wed, 17 Jun 2015 16:50:56 GMT

No, it's not because you typed the field names. It's the CrossTable function that turns the field names into text values.

Re: Handling dates and cross table

Wed, 08 Jul 2015 09:19:11 GMT

Thanks a lot !

Re: Handling dates and cross table

alibd225 — Sat, 08 Aug 2015 07:51:33 GMT

Thank you so much...