topic Re: Max Date issue in QlikView

Max Date issue

Fri, 12 Feb 2016 18:21:31 GMT

I have a problem with Max Date functionality. I have a file with Items and costing dates. I would like to get the last Costing date for the different items.

I would like to do it in the script, but function MAX is not ok. I get error message.

My Qlikview version is connected to Movex/M3

MCCOMA: // Load Max Date

LOAD

KPCONO AS [KPCONO - Company],

KPFACI as [KPFACI - Facility],

TEXT(KPITNO) as [IA_EZ_Item Number],

KPSTRT as [KPSTRT - Product structure type],

KPPCTP as [KPPCTP - Costing type],

Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD') as [KPPCDT - Costing date],

I have tried Max formula both in script and in Chart but I have always errors coming in.

in my script, for date format, I have

SET TimeFormat='hh:mm:ss';

SET DateFormat='YYYY-MM-DD';

Thanks for your help

Re: Max Date issue

Fri, 12 Feb 2016 18:46:06 GMT

Can you please share your full script snippet.

Re: Max Date issue

Fri, 12 Feb 2016 18:52:51 GMT

I have done this

MCCOMA: // Load Max Date

LOAD

KPCONO AS [KPCONO - Company],

KPFACI as [KPFACI - Facility],

TEXT(KPITNO) as [IA_EZ_Item Number],

KPSTRT as [KPSTRT - Product structure type],

KPPCTP as [KPPCTP - Costing type],

Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD') as [KPPCDT - Costing date],

KPCA01 as [KPCA01 - Value Component A01],

KPCA02 as [KPCA02 - Value Component A02],

KPCA03 as [KPCA03 - Value Component A03],

KPCB01 as [KPCB01 - Value Component B01],

KPCB02 as [KPCB02 - Value Component B02],

KPCB03 as [KPCB03 - Value Component B03],

KPCB04 as [KPCB04 - Value Component B04]

;

SQL SELECT KPCONO, KPFACI, KPITNO, KPSTRT, KPPCTP, KPPCDT, KPCA01, KPCA02, KPCA03, KPCB01, KPCB02, KPCB03, KPCB04

FROM S658685C.MVXCDTA020.MCCOMA

WHERE KPCONO = 020 AND KPFACI = 'HUP' AND KPPCTP = '3' AND KPPCDT >= '20160101'

ORDER BY KPCONO, KPFACI,KPITNO,KPSTRT ;

Re: Max Date issue

sunny_talwar — Fri, 12 Feb 2016 19:11:33 GMT

What is the exact error you are seeing?

Re: Max Date issue

Fri, 12 Feb 2016 19:16:41 GMT

Every time, I put Max(Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD')), I get the message

"Invalid Expression" and the file is not loaded

Re: Max Date issue

sunny_talwar — Fri, 12 Feb 2016 19:32:34 GMT

That is because aggregation functions such as Max, Sum, Avg need Group By statement to aggregate date. Are you trying to find the max for the whole date or by certain field such as max date by country for example?

Re: Max Date issue

Fri, 12 Feb 2016 19:55:35 GMT

in fact I want to get the max date by item.

In this file we have

Item Date Cost

A 20150101 10

A 20151201 9

A 20160101 8

I would like the last one

Re: Max Date issue

sunny_talwar — Fri, 12 Feb 2016 21:24:53 GMT

So something like this:

Table:

LOAD Item,

Date,

Cost

FROM Source;

Right Join(Table)

LOAD Item,

Max(Date) as Date

Resident Table

Group By Item;

Thanks for pointing out the error sfatoux72‌

Re: Max Date issue

sfatoux72 — Fri, 12 Feb 2016 21:32:08 GMT

‌Sunny, you need to group by Item, not by Table.

Re: Max Date issue

sunny_talwar — Fri, 12 Feb 2016 21:38:35 GMT

Hahahaha you are right

Re: Max Date issue

Fri, 12 Feb 2016 23:27:20 GMT

Hello

Thanks all for your answers.

I will try on Monday and I will let you know.

Have a nice week-end

Luc

Re: Max Date issue

Mon, 15 Feb 2016 14:14:24 GMT

Hello

Thanks for your help

I was finally able to get the proper extract, I have now this

MCCOMA: // Load Max Date

LOAD

KPFACI & '|' & TEXT(KPITNO) & '|' & Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD') AS KEY_MCCOMA_MCCOMA2,

KPCONO ,

KPFACI ,

TEXT(KPITNO) AS [IA_EZ_Item Number],

KPSTRT ,

KPPCTP ,

KPPCDT as Date,

Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD') AS Date_2,

KPCA01 ,

KPCA02 ,

KPCA03 ,

KPCB01 ,

KPCB02 ,

KPCB03 ,

KPCB04 ;

SQL SELECT KPCONO, KPFACI, KPITNO, KPSTRT, KPPCTP, KPPCDT, KPCA01, KPCA02, KPCA03, KPCB01, KPCB02, KPCB03, KPCB04

FROM S658685C.MVXCDTA020.MCCOMA

WHERE KPCONO = 020 AND KPFACI = 'HUP' AND KPPCTP = '3'

ORDER BY KPCONO,KPFACI,KPITNO,KPSTRT ;

MCCOMAMaxDate: //

RIGHT KEEP (MCCOMA)

LOAD

KPFACI & '|' & [IA_EZ_Item Number] & '|' & MAX(Date(Date#([Date],'YYYYMMDD'),'YYYY-MM-DD')) AS KEY_MCCOMA_MCCOMA2,

KPFACI as [KPFACI - Facility],

[IA_EZ_Item Number] as [IA_EZ_Item Number_1],

MAX(Date(Date#([Date],'YYYYMMDD'),'YYYY-MM-DD')) as [KPPCDT - Costing date]

RESIDENT MCCOMA

GROUP BY KPFACI,[IA_EZ_Item Number];

It works fine now

Have a nice week

Luc