Value Load

[#1]

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

[ashfaq_haseeb]

Try like this

LOAD Year,

     Sales

FROM

NEw.xlsx

(ooxml, embedded labels, table is Sheet1)

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

Regards

ASHFAQ

[#3]

Thnaks for reply

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

[CELAMBARASAN]

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);

[ashfaq_haseeb]

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

[ashfaq_haseeb]

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

[CELAMBARASAN]

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

[#8]

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;