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: 
sarbnyota001
Partner - Contributor
Partner - Contributor

Load selective data

Hi guys,

wonder if you can help me on this.

I am trying to load data but only for last 10 years. I cant figure out what am i doing wrong here. Can you please help out?

Last10Years:

Load

Year("Date") as [Year],

1 AS Flag_Last10years

FROM [$(vDataFolder)DAT_FileData\Data Folder\GBP Data.xlsx]

        (ooxml, embedded labels, header is 3 lines, table is GBP)

       

Where Year("Date") >= Max(Year("Date")) -10

;

Can you please help out?

Thanks

6 Replies
sunny_talwar

May be something like this:

Last10Years:

LOAD Year("Date") as [Year],

          1 as Flag_Last10years

FROM [$(vDataFolder)DAT_FileData\Data Folder\GBP Data.xlsx]

(ooxml, embedded labels, header is 3 lines, table is GBP);

Right Join (Last10Years)

LOAD Max(Year) - 11 + IterNo() as Year

Resident Last10Years

Where IterNo() <= 10;

sarbnyota001
Partner - Contributor
Partner - Contributor
Author

Hi Sunny,

That doesnt work, only brings in one year.

I have attached the qvw file. See if you can fix it on there.

I have to load data for all years, then in my expressions i have to use last 20, 15, 10,5 years separately. i.e there will a chart showing last 15 years, another chart showing 10 years and so on. I want to load each of them separately so that in my set analysis i can use rowno() rather than hardcoding them to a year else end user will have to change it every year.

can you help on this?

Regards,

harishkumarg
Creator III
Creator III

Hi

You can use the following filter condition,

Where Date>=YearStart(Today(),-10);

This will give your last 10 years data based on calendar date.

Please find the attached QVW for reference.

Reagrds

Harish

sarbnyota001
Partner - Contributor
Partner - Contributor
Author

Moderators, can you please approve this so that we can have replies on it??

Anonymous
Not applicable

If the "last year" is always the current calendar year, you can use year(today()), and the second load is not needed:

WHERE year("Date") >= year(today()) -10

sunny_talwar

May be try this:

Right Join (AllYears)

LOAD MaxYear - 11 + IterNo() as Year,

  Iterno() as LineID

While IterNo() <= 10;

LOAD Max(Year) as MaxYear

Resident AllYears;