Max Value column

[#1]

Hi Experts

I'm loading from an excel sheet that gets updated month on month see headers below:

Team     Unit     Currency     Jan15     Feb15     Mar15

The script is load * from xls

But I need to be able to identify the Max Month as per column header as in my charts I will be comparing LAST month ( which will be the last column and the MAX Date) with other months

Can anyone advise how I may do this?

Thanks
A

[sunny_talwar]

I think the best idea would be to use a CROSS TABLE load to bring the months as rows instead of column header and then you can easily use set analysis to achieve what you are trying to do.

HTH

Best,

S

[#3]

Hi Sunindia

I was thinking that but theres loads of vaiable data in the other ciolumns that would make coding harder I think

Is that normally the only way to do it - im not overally familiar with Cross tables

Thanks
A

[sunny_talwar]

To determine the maximum date, I can think of other ways. But not really sure how you will use them within your set analysis statement. You will basically have to change the expression as and when the new data comes in. CROSS TABLE LOAD is not that difficult. QlikView has sort of wizard which is quite interactive. Additionally you will be able to find a lot of discussions on the community regarding the same.

Best,

S

[chrislofthouse]

Hi Anne,

I agree with Sunindia, the most efficient way would be to use a Cross Table, you could then use set analysis as Sunindia mentions or use peek and put the value in the script if your requirements allow this method.

Attached a very quick example. ! !

Regards,

Chris

[#6]

Hi Anne,

not sure to understand your need.

do you want just flag the last month after reload ?

if so

T:

CrossTable(Team,unit, ...  )  -- use the cross table assistant to right the script

LOAD Team, Unit, Currency,   Date   (you must format your date either as 201503, 201502... or date format like DD/MM/YY to get the right max(date) later)

FROM Table;

Tmp:

LOAD [Tema, Unit, ...if needed] Date, max(Date) as MaxDate  resident T

group [Tema, Unit, ...if needed]  by Date  --   will return 201503  or equivalent number to 01/03/2015

JOIN (T)

LOAD [Tema, Unit, ...if needed]  Date, MaxDate resident Tmp;

drop tmp

best regards

Chris