topic Max Value column in QlikView

Max Value column

Thu, 05 Mar 2015 13:06:22 GMT

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

Re: Max Value column

sunny_talwar — Thu, 05 Mar 2015 13:19:10 GMT

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,

Re: Max Value column

Thu, 05 Mar 2015 13:33:00 GMT

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

Re: Max Value column

sunny_talwar — Thu, 05 Mar 2015 13:40:34 GMT

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,

Re: Max Value column

chrislofthouse — Thu, 05 Mar 2015 13:43:37 GMT

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

Re: Max Value column

Thu, 05 Mar 2015 13:51:39 GMT

Hi Anne,

not sure to understand your need.

do you want just flag the last month after reload ?

if so

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