Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Unable to reformat date imported from Excel

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.

typeKPIjanv-14janv-15
1A1112
2B2122


year is empty.png

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;

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
reddy-s
Master II
Master II

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;

reddy-s
Master II
Master II

If you face any issue with it, attach a sample file and I will help you with it.

Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you so much Gysbert for that trick!

I would never had figured it out.

+ I'm glad I mentionned the crosstable 🙂

Not applicable
Author

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

Not applicable
Author

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.