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: 
NavinReddy
Creator II
Creator II

How to create year field

Dear All,

I have month wise but i want to create year Field

please find the attechment

i tried with cross table but dont know how to achive it

the same way i have data 2012,2013 also have

please some one help me to do this

Thanks In Advance

Niranjan

1 Solution

Accepted Solutions
MarcoWedel

Hi Niranjan,

given the data for different years resides in seperate files with filenames ending with the year number, one solution could be also:

Set LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

table1:

CrossTable (monthtemp, value, 3)

LOAD Right(FileBaseName(), 4) as year, *

FROM [QlikCommunity_Thread_122484_*.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(table1)

LOAD Distinct

  monthtemp,

  Date#(monthtemp, 'MMM') as month

Resident table1;

DROP Field monthtemp;

QlikCommunity_Thread_122484_Pic1.JPG.jpg

hope this helps

regards

Marco

View solution in original post

5 Replies
joshabbott
Creator III
Creator III

So you have three separate spreadsheets for three separate years?  Couldn't you just add a 'Year' column to the spreadsheet is my though?

Not applicable

Hi,

If value of my year field were actual year... I just get year value with this:

Year(Today()) as Year,

Best Regards.

NavinReddy
Creator II
Creator II
Author

greate thought

Thanks you somuch really its help full i wil try


MarcoWedel

Hi Niranjan,

given the data for different years resides in seperate files with filenames ending with the year number, one solution could be also:

Set LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

table1:

CrossTable (monthtemp, value, 3)

LOAD Right(FileBaseName(), 4) as year, *

FROM [QlikCommunity_Thread_122484_*.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(table1)

LOAD Distinct

  monthtemp,

  Date#(monthtemp, 'MMM') as month

Resident table1;

DROP Field monthtemp;

QlikCommunity_Thread_122484_Pic1.JPG.jpg

hope this helps

regards

Marco

MarcoWedel

Hi,

please close this thread if there are no further questions.

Thanks

Regards

Marco