topic Re: Value Load in QlikView

Value Load

Thu, 20 Nov 2014 09:32:46 GMT

Hi,

I need to load only the max and previous year sales values

i.e 2014 and 2013 year sales values and discard the other years sales.

attached the sample app and excel sheet

Re: Value Load

ashfaq_haseeb — Thu, 20 Nov 2014 09:36:53 GMT

Try like this

LOAD Year,

Sales

FROM

NEw.xlsx

(ooxml, embedded labels, table is Sheet1)

where Year>= Year(today())-1;

Regards

ASHFAQ

Re: Value Load

Thu, 20 Nov 2014 09:38:22 GMT

Thnaks for reply

how to write Max(Year) in where condition for load

Re: Value Load

CELAMBARASAN — Thu, 20 Nov 2014 09:46:19 GMT

You cann't take a Max year in where condition directly.

Try kind of like this

MaxYear:

LOAD Max(Year) AS MaxYear

FROM

NEw.xlsx

(ooxml, embedded labels, table is Sheet1)

LET vMaxYear = Peek('MaxYear');

Drop Table MaxYear;

Data:

LOAD Year,

Sales

FROM

NEw.xlsx

(ooxml, embedded labels, table is Sheet1)

where Year>= ($(vMaxYear)-1);

Re: Value Load

ashfaq_haseeb — Thu, 20 Nov 2014 09:56:50 GMT

Hi,

You can try by storing max year in a variable and load directly.

LET vMaxYear = 2014;

Data:

LOAD Year,

Sales

FROM

NEw.xlsx

(ooxml, embedded labels, table is Sheet1)

where Year>= ($(vMaxYear)-1);

Hope it helps

Regards

ASHFAQ

Re: Value Load

ashfaq_haseeb — Thu, 20 Nov 2014 10:14:16 GMT

HI,

With the above code you will get for both years.

If you cant do that then you need to go with above solution proposed by celambarasan

Regards

ASHFAQ

Re: Value Load

CELAMBARASAN — Thu, 20 Nov 2014 10:29:49 GMT

Load only the YEAR column to find the MAX. After getting the MAX load all the columns with condition.

Re: Value Load

Thu, 20 Nov 2014 10:40:15 GMT

Check This

TemporarySalesTable:

LOAD

Year

,Sales

FROM

[NEw.xlsx]

(ooxml, embedded labels, table is Sheet1);

MaxYear:

LOAD

Max(Year) as MaxYear

Resident TemporarySalesTable;

LET vMaxYear = Peek('MaxYear',0,'MaxYear');

LET vPreviousYear = $(vMaxYear)-1;

NoConcatenate

SalesTable:

LOAD

Year

,Sales

Resident TemporarySalesTable

Where Year >= $(vPreviousYear);

DROP Table TemporarySalesTable;

DROP Table MaxYear;