topic Re: Max Date Values in QlikView

Max Date Values

Fri, 06 Mar 2015 08:59:57 GMT

Hi Expert,

I have data with multiple date but I need the data with maximum date values.

Could you please suggest?

Re: Max Date Values

chrislofthouse — Fri, 06 Mar 2015 09:05:40 GMT

Use MAX() function.

Re: Max Date Values

Fri, 06 Mar 2015 09:13:26 GMT

My script is like below

DisplayOrder:
LOAD
    D_AS_OF_TMS,
    C_ORDER,
    CLM_CORE_NONCOR,
    CLM_SEGMENT,
    CLM_SEGMENT_NEW,
    COMMENTS;
SQL
    SELECT
D_AS_OF_TMS ,
    C_ORDER,
    CLM_CORE_NONCOR,
    CLM_SEGMENT,
    CLM_SEGMENT_NEW,
    COMMENTS;
FROM CCAR.dbo.MICCAR_Disp_Order;

where to use it now

Re: Max Date Values

puttemans — Fri, 06 Mar 2015 09:13:30 GMT

Hi Raj,

I can imagine you need the maximum date per variable x. In that case, you use the MAX() function together with a group by.

e.g.

MAP_KtM:

MAPPING LOAD
KtM,

MAX(ShareNew) as ShareNewmax
FROM xxx (qvd)
Group by KtM;

In this example, I select out of a big table the maximum of 'ShareNew' for each KtM (variable I 'group by' , and recall it to 'ShareNewmax'.

I typically use this as a mapping table, in order to bring this maximum back to all lines of the total table through applymap.

Re: Max Date Values

avinashelite — Fri, 06 Mar 2015 09:13:59 GMT

Hi ,

If you want you have on any field: then use aggr() function

AGGR(max(date),field)

if your trying to achieve this in the script try like this:

LOAD

field1,

max(date) as date;

from tablename group by field1;

Hope this helps you .

Re: Max Date Values

puttemans — Fri, 06 Mar 2015 09:15:40 GMT

And what is your 'Date' field? You just want the maximum out of the total, or per a given other variable?

Re: Max Date Values

Fri, 06 Mar 2015 09:44:26 GMT

I made it like this:

FROM CCAR.dbo.MICCAR_Disp_Order
WHERE AS_OF_TMS = (select max(AS_OF_TMS) from CCAR.dbo.MICCAR_Disp_Order where AS_OF_TMS <= getDate());

Note = AS_OF_TMS, is my date field