Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Max Date issue

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

1 Solution

Accepted Solutions
Not applicable

Re: Max Date issue

Hello

Thanks all for your answers.

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

Have a nice week-end

Luc

11 Replies
Not applicable

Re: Max Date issue

Can you please share your full script snippet.

Not applicable

Re: Max Date issue

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
Not applicable

Re: Max Date issue

What is the exact error you are seeing?

Not applicable

Re: Max Date issue

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
Not applicable

Re: Max Date issue

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?

Not applicable

Re: Max Date issue

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
Not applicable

Re: Max Date issue

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
Not applicable

Re: Max Date issue

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

sunny_talwar
Not applicable

Re: Max Date issue

Hahahaha you are right