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 · Accepted solution]

Hello

Thanks all for your answers.

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

Have a nice week-end

Luc

[#3]

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