Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Unable to reformat date imported from Excel

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
6 Replies
reddys310
Honored Contributor II

Re: Unable to reformat date imported from Excel

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;

reddys310
Honored Contributor II

Re: Unable to reformat date imported from Excel

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

Re: Unable to reformat date imported from Excel

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

Re: Unable to reformat date imported from Excel

Thank you so much Gysbert for that trick!

I would never had figured it out.

+ I'm glad I mentionned the crosstable :-)

Not applicable

Re: Unable to reformat date imported from Excel

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

Re: Unable to reformat date imported from Excel

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.

Community Browser