topic Re: Load selective data in QlikView

Load selective data

sarbnyota001 — Sat, 24 Sep 2016 11:21:13 GMT

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

Re: Load selective data

sunny_talwar — Sat, 24 Sep 2016 20:31:35 GMT

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;

Re: Load selective data

sarbnyota001 — Sat, 24 Sep 2016 22:17:24 GMT

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,

Re: Load selective data

harishkumarg — Sun, 25 Sep 2016 06:48:51 GMT

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

Re: Load selective data

sarbnyota001 — Sun, 25 Sep 2016 08:06:35 GMT

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

Re: Load selective data

Anonymous — Mon, 26 Sep 2016 13:39:08 GMT

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

Re: Load selective data

sunny_talwar — Mon, 26 Sep 2016 15:56:49 GMT

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;