Handling dates and cross table

[#1]

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

[Gysbert_Wassenaar]

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

[#3]

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

[sunny_talwar]

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....

[Gysbert_Wassenaar]

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

[#6]

Thanks a lot !

[alibd225]

Thank you so much...