Max Date issue

[#1]

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

[#2]

Can you please share your full script snippet.

[#3]

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 ;

[sunny_talwar]

What is the exact error you are seeing?

[#5]

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

"Invalid Expression" and the file is not loaded

[sunny_talwar]

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?

[#7]

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

[sunny_talwar]

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‌

[sfatoux72]

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

[sunny_talwar]

Hahahaha you are right