Discussion board where members can get started with Qlik Sense.
I'm importing data from Excel that includes date information (YYYY-MM).
I need to get 2 new separate columns for YYYY and MM.
Unfortunately, whatever I try in QlikView and Qlik Sense it results in empty data for the formatted date.
I used date formatting successfully in other scripts (e.g. year([Day]) as "Year") but this time around it just doesn't work and I have no clue why.
Here is my script (I pasted everything even if don't think my issue has anything to do with the crosstable, because even when I try to use main assets I have the same issue).
NB: I also tried using the Date function - Date([Month], 'YYYY') as "year" but it didn't make a difference
CrossTable(Month, Data, 2)
LOAD * FROM
[lib://IT KPIs Data\Funds Admin\KPIs.xlsx]
(ooxml, embedded labels, table is Feuil1);
Year([Month]) as "year",
drop table initial;
Go to Solution.
The CrossTable load probably turns the numbers into text values. You can turn these back into numbers with the num# value. So try this:
Year(num#([Month])) as "year",
The Year functions not working because the Month field is not a date. First convert the date string to Internal Date using the Date# function and make use of the Year function.
Try this code:
load * Inline [
load date#(xDate,'YYYY-MM-DD') as rDate,
Month(date#(xDate,'YYYY-MM-DD')) as rMonth,
year(date#(xDate,'YYYY-MM-DD')) as rYear;
load form &'-01' as xDate
drop table X;
If you face any issue with it, attach a sample file and I will help you with it.
Thank you so much Gysbert for that trick!
I would never had figured it out.
+ I'm glad I mentionned the crosstable :-)
Try this one:
load * INLINE
MonthStart(date& '-01') as fullDate,
Year(MonthStart(date& '-01')) as year,
Month(MonthStart(date& '-01')) as month
drop table initial
I have attached the example for you.
Thanks Sangram. I had no clue it was seeing as text. (especially because in Excel it's clearly a date and I could see it beeing converted to a number).
Thank to you so much for sharing that information and the convertion trick.