Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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])
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....
No, it's not because you typed the field names. It's the CrossTable function that turns the field names into text values.
Thanks a lot !
Thank you so much...