Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hello
Thanks all for your answers.
I will try on Monday and I will let you know.
Have a nice week-end
Luc
Can you please share your full script snippet.
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 ;
What is the exact error you are seeing?
Every time, I put Max(Date(Date#([KPPCDT],'YYYYMMDD'),'YYYY-MM-DD')), I get the message
"Invalid Expression" and the file is not loaded
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?
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
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
Sunny, you need to group by Item, not by Table.
Hahahaha you are right