topic Re: Max date with apply map in QlikView

Max date with apply map

Tue, 06 May 2014 10:41:59 GMT

Hello;

I am working on a set of data containing multiple dates. The goal is for each item to define what the maximum date is.

I am using therefore the apply map function.

Could you please let me know if this is the most efficient methodology to do so.

Many thanks in advance for your help and have a great day.

Kristel.

Attached you will find the spreadsheet used as an example and the qvd file.

Hope you will be able to read this is.

If not here is the script;

Regiongrouping:

mapping load

Region,

(max(orderdate))

FROM

(biff, embedded labels)

group by Region;

OderData:

LOAD Region,

orderdate,

applyMap ('Regiongrouping', Region) as [Max oderdate],

value,

comment

FROM

(biff, embedded labels);

To make sure the load is focussing on the max date only we could close with:

Where orderdate=[Max oderdate];

But is it the best way???

Re: Max date with apply map

Anonymous — Tue, 06 May 2014 12:37:21 GMT

I'd do it a little bit simpler, with one time access to the source data and no applymap:

OderData:
LOAD
     Region,
     orderdate as [Max oderdate],
     value,
     comment
FROM (biff, embedded labels);

INNER JOIN (OderData) LOAD
Region,
max(orderdate) as [Max oderdate]
RESIDENT OderData
GROUP BY Region;

Re: Max date with apply map

Tue, 06 May 2014 13:26:47 GMT

Another possibility would be to do this:

OrderData:

LOAD Region,

orderdate,

Region&'-'&date(orderdate,'YYYYMMDD') as [RegionIndex],

value,

comment

FROM

(biff, embedded labels);

RegionLast:

LOAD Region,

max(orderdate) as [Max order date]

Resident OrderData

Group by Region;

RegionLast1:

load

Region&'-'&date([Max order date],'YYYYMMDD') as RegionIndex,

'1' as Region_LastFlag

Resident RegionLast;

DROP Table RegionLast;

left join (OrderData)

load

RegionIndex,

Region_LastFlag as Region_IsLast

resident RegionLast1;

drop table RegionLast1;

RegionDetailsLast:

load

Region,

orderdate,

value,

Region_IsLast,

comment

resident OrderData

where Region_IsLast = '1';

drop table OrderData;

What do you think?

And now when you have different scenario, how do you evaluate them on efficiency?

Thanks.

Kristel

Re: Max date with apply map

Tue, 06 May 2014 14:14:03 GMT

Hello Kristel,

Instead of doing it so complex,

just do one thing.Use the aggr() function.

No need to use any mapping load.Use a straight table .add one more calculated dimension.in that use the aggr() function.In the expression just write any 1 and then hide the expression.You will find your desired answer.

the aggr() function will be like =aggr(max(orderdate),Region)

Hope u understood.

Re: Max date with apply map

Anonymous — Tue, 06 May 2014 15:26:01 GMT

Kristel,

I don't see why do you need a long and complex script while you can have it short and simple.

Biren Agrawalla,
As a rule, it is better to have actual fields rather than calculated dimensions - same result but better performance. Still your solution makes sense if the max date is not the absolute max date per Region but within current date selection. Even in this case, I'd rather move this expression from calculated dimension to expression. And, in expression, no need to aggr() because Region is a dimension already, simply:
max(orderdate)

Re: Max date with apply map

jaimeaguilar — Tue, 06 May 2014 15:53:51 GMT

Hi,

the aggr() approach is a fast solution to get results on the fly. However, as data volume increases, having a calculated dimension can become so heavy for your application, impacting in performance times. So if you have access to script is better to make as much pre-calculations as you can in script,

regards

Re: Max date with apply map

Wed, 07 May 2014 09:18:36 GMT

Hi Micheal,

Thanks for the solution.But I know this is not a properway of doing as we have many other ways as well.

But I just posted my thought because this can also be a way of solving.

Its better if we will come to know about as many possible types of solutions for a problem.

Thanks

Regards,

Biren