Max date of good receipt

[#1]

Dear Qlik view communnity,

I am new with Qlik view, I need suggestions to resolve this issue.

I'll try to explain:

I have a table with these fields (downloaded from SAP)

N Purchase Order document

Positions

Warehouse movement

Date of Good receipt

The warehouse movement could be

101: entrance

102: writing off

This is an example:

Number of PO	Position	Warehose movement	Date of good receipt Note
44000xxxx	001	101	21/11/2012
44000xxxx	001	102	21/11/2012
44000xxxx	001	101	22/11/2012

I need only the last entrance.

So I tried in this way:

Load [date of good receipt note],

Date(Max([Date of good receipt note]) as [Date Max]

Resident [name of table]

Group by [PO number, Position];

But this doesn't work.

The error is this:

Moreover could happens that in the same day I have the entrance of goods, the writing off and the entrance again.

Could you help me please?

Let me know if you need more details.

Thanks

[MayilVahanan · Accepted solution]

HI

When u use aggregate function, you must use group by all other field except the aggregate function field..

Ex:

Load A,B,C,Max(D) as D resident tablename group by A,B,C;

or

u can use like this

tablename:

Load A,B,C,D from table1;

inner join(tablename)

Load A,Max(D) as D resident tablename group by A;

Hope it helps

[#3]

Hi,

thank you for your help.

I used the inner join and now it works.

Can I make another question?

Now I have this:

How can I delete the first row, where Date of Good receipt (BLDAT4) is smaller ?

[MayilVahanan]

Hi

Use Max(BlDat4) in expression..

Hope it helps