Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
type | KPI | janv-14 | janv-15 |
1 | A | 11 | 12 |
2 | B | 21 | 22 |
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
initial:
CrossTable(Month, Data, 2)
LOAD * FROM
[lib://IT KPIs Data\Funds Admin\KPIs.xlsx]
(ooxml, embedded labels, table is Feuil1);
final:
LOAD
KPI,
Data,
Month,
Year([Month]) as "year",
RESIDENT initial;
drop table initial;
The CrossTable load probably turns the numbers into text values. You can turn these back into numbers with the num# value. So try this:
initial:
CrossTable(Month, Data, 2)
LOAD * FROM
[lib://IT KPIs Data\Funds Admin\KPIs.xlsx]
(ooxml, embedded labels, table is Feuil1);
final:
LOAD
KPI,
Data,
Month,
Year(num#([Month])) as "year",
RESIDENT initial;
Hi Oliver,
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:
X:
load * Inline [
form
2015-10
2015-11
2015-12
];
NoConcatenate
Y:
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
resident X;
drop table X;
If you face any issue with it, attach a sample file and I will help you with it.
The CrossTable load probably turns the numbers into text values. You can turn these back into numbers with the num# value. So try this:
initial:
CrossTable(Month, Data, 2)
LOAD * FROM
[lib://IT KPIs Data\Funds Admin\KPIs.xlsx]
(ooxml, embedded labels, table is Feuil1);
final:
LOAD
KPI,
Data,
Month,
Year(num#([Month])) as "year",
RESIDENT initial;
Thank you so much Gysbert for that trick!
I would never had figured it out.
+ I'm glad I mentionned the crosstable 🙂
Hello.
Try this one:
initial:
load * INLINE
[id,date
1,'2015-01'
2,'2015-02'
3,'2015-03'
4,'2015-04'
];
final:
load id,
date,
MonthStart(date& '-01') as fullDate,
Year(MonthStart(date& '-01')) as year,
Month(MonthStart(date& '-01')) as month
resident initial;
drop table initial
I have attached the example for you.
Att.
Renato Adolfs
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.