Max data on the basis of field

[swatitomar]

Hi Team,

Want to find  the max date on the basis of type Del and Serial no.

Like to show only max Del Data .

Input:

Qty	Type	Date	Serial_no	Dealer
1	Del	13/05/2017	A1	D01
1	Delrt	15/05/2017	A1	D01
1	Del	12/08/2017	A2	D02
1	Del	13/08/2017	A3	D03
1	Delrt	14/05/2017	A3	D03
1	Del	15/08/2017	A3	D04
1	Del	16/08/2017	A4	D05
1	Delrt	16/08/2017	A4	D05
1	Del	17/08/2017	A4	D05
1	Del	18/08/2017	A5	D06
1	Delrt	19/08/2017	A5	D06
1	Del	19/08/2017	A5	D06

  output

Qty	Type	Date	Serial_no	Dealer
1	Del	12/08/2017	A2	D02
1	Del	15/08/2017	A3	D03
1	Del	17/08/2017	A4	D05
1	Del	19/08/2017	A5	D06

Can anyone provide the solution.

Thanks in Advance..

[swatitomar]

Hi Sunny,

Thank you so much for your reply..

But I Want unique serial no. data and I think with the help of rowno() we can achieve it but not sure.

In short I want unique Serial Del type data but only latest del data.

Some Example:

For A3 serial no.  for last Del line should come i.e. 15./8/2017 only and

For A1 nothing should show as after Del , Delrt take place so don't want this line

For A4 16/10/2017 should come .

Qty, Type, Date, Serial, Dealer

1, Del, 13/08/2017, A3, D03

    1, Delrt, 14/05/2017, A3, D03

    1, Del, 15/08/2017, A3, D04

    1, Delrt, 15/08/2017, A3, D04

    1, Del, 15/08/2017, A3, D04

    1, Del, 13/05/2016, A1, D01
    1, Delrt, 15/05/2016, A1, D01

    1, Del, 13/08/2017, A4, D03

    1, Delrt, 14/05/2017, A4, D03

    1, Del, 16/10/2017, A4, D04

[sunny_talwar]

What is not right about the output you see in the attached file above?

[swatitomar]

Hi Sunny,

My purpose is to show Detail of Delivered item against multiple dimension like dealer, City , State , Serial no

1. The output shared by you showing duplicate Serial No. ,and I want Max Del date data only

Ex:

Qty, Type, Date, Serial, Dealer

1, Del, 13/08/2017, A3, D03

    1, Delrt, 14/05/2017, A3, D03

    1, Del, 15/08/2017, A3, D04

    1, Delrt, 15/08/2017, A3, D04

    1, Del, 15/05/2018, A3, D04

Output: In year 2018 record of A3 Serial against dealer D04 will come not in year 2017 . Single Entry

2. 1 more point as I mentioned in above reply . if I change the input like Del den Delrt , here in this case I want any data as Del date< Delrt Date.

Ex: Input

Qty, Type, Date, Serial, Dealer

1, Del, 13/05/2016, A1, D01

1, Delrt, 15/05/2016, A1, D01

Output: No record for A1 serial will come

3. It should work on the basis of Serial not Dealer . Should show the latest del data against Serial .

Ex:

Qty, Type, Date, Serial, Dealer

1, Del, 13/08/2017, A4, D03

1, Delrt, 14/05/2017, A4, D03

1, Del, 16/10/2017, A4, D04 

Output: (This line should come as last line is Del for Serial no. A4 with dealer D04 .If user choose year 2017 or 10 month of 2017)

[sunny_talwar]

One thing which has been confusing me is the fact that you mentioned that you only wanted to see Del, why is Delrt  showing up on your output above? I am somehow not able to follow the logic... did we misunderstood that you only needed Del?

[swatitomar]

1. Where in output it is Delrt ??

2. Yes , I want to show Latest Del Data only against the Serial no. .

Note:

>>>If after Delivery item get return and it has no further transaction den it will not come.(Del Date< Delrt Date will not show this entry)

>>> Single Item can deliver multiple times but after every delivery , return should come den only that particular item can del again.

So in above examples I'm showing the same.

[sunny_talwar]

These rows are Delrt

Are these not the output? I might be going crazy, but I thought this is the output you are looking to get . If this isn't the output... would you be able to give me the exact rows that will be part of the output from these

Thanks,

Sunny

[swatitomar]

That was an Input not output.

I have marked the output with yellow color

For below table:

LOAD * INLINE [

    Qty, Type, Date, Serial, Dealer

    1, Del, 13/05/2016, A1, D01

    1, Delrt, 15/05/2016, A1, D01

    1, Del, 12/08/2017, A1, D01

    1, Del, 13/08/2017, A3, D03

    1, Delrt, 14/05/2017, A3, D03

    1, Del, 15/08/2017, A3, D04

    1, Delrt, 15/08/2017, A3, D04

    1, Del, 15/05/2018, A3, D04

    1, Del, 16/08/2017, A4, D05

    1, Delrt, 16/08/2017, A4, D05

    1, Del, 17/08/2017, A4, D05

    1, Del, 18/08/2017, A5, D06

    1, Delrt, 19/08/2017, A5, D06

    1, Del, 19/08/2017, A5, D06

    1, Del, 13/05/2017, A6, D01

    1, Delrt, 15/06/2017, A6, D01

    1, Del, 12/08/2017, A6, D01

];

Output will Be

Qty, Type, Date, Serial, Dealer

    1, Del, 12/08/2017, A1, D01

     1, Del, 15/05/2018, A3, D04

     1, Del, 17/08/2017, A4, D05

    1, Del, 19/08/2017, A5, D06

    1, Del, 12/08/2017, A6, D01

Now you can see only last Del data against the Serial .I'm saying against the serial no. because in above ex. For Serial no. A3 dere are 2diff dealer but I'm showing data of only Dealer which the latest Del data..

Sorry for so much of confusing..:(

[sunny_talwar]

Try this

Table:

LOAD * INLINE [

    Qty, Type, Date, Serial, Dealer

    1, Del, 13/05/2016, A1, D01

    1, Delrt, 15/05/2016, A1, D01

    1, Del, 12/08/2017, A1, D01

    1, Del, 13/08/2017, A3, D03

    1, Delrt, 14/05/2017, A3, D03

    1, Del, 15/08/2017, A3, D04

    1, Delrt, 15/08/2017, A3, D04

    1, Del, 15/05/2018, A3, D04

    1, Del, 16/08/2017, A4, D05

    1, Delrt, 16/08/2017, A4, D05

    1, Del, 17/08/2017, A4, D05

    1, Del, 18/08/2017, A5, D06

    1, Delrt, 19/08/2017, A5, D06

    1, Del, 19/08/2017, A5, D06

    1, Del, 13/05/2017, A6, D01

    1, Delrt, 15/06/2017, A6, D01

    1, Del, 12/08/2017, A6, D01

];

Right Join (Table)

LOAD Serial,

Type,

Max(Date) as Date

Resident Table

Where Type = 'Del'

Group By Serial, Type;

[swatitomar]

It is working fine but Sorry Sunny i forget to mention one scenario which one is imp.

Table:

LOAD * INLINE [

    Qty, Type, Date, Serial, Dealer

    1, Del, 13/05/2017, A6, D01

    1, Delrt, 15/06/2017, A6, D01

];

Output: This record should not come as in this case " Delrt Date> Del Date"..

i have mentioned it in above replies but forget in last reply..

[sunny_talwar · Accepted solution]

May be try this

Table:

LOAD * INLINE [

    Qty, Type, Date, Serial, Dealer

    1, Del, 13/05/2016, A1, D01

    1, Delrt, 15/05/2016, A1, D01

    1, Del, 12/08/2017, A1, D01

    1, Del, 13/08/2017, A3, D03

    1, Delrt, 14/05/2017, A3, D03

    1, Del, 15/08/2017, A3, D04

    1, Delrt, 15/08/2017, A3, D04

    1, Del, 15/05/2018, A3, D04

    1, Del, 16/08/2017, A4, D05

    1, Delrt, 16/08/2017, A4, D05

    1, Del, 17/08/2017, A4, D05

    1, Del, 18/08/2017, A5, D06

    1, Delrt, 19/08/2017, A5, D06

    1, Del, 19/08/2017, A5, D06

    1, Del, 13/05/2017, A6, D01

    1, Delrt, 15/06/2017, A6, D01

];

Right Join (Table)

LOAD Serial,

'Del' as Type,

Date

Where Len(Trim(Date)) > 0;

LOAD Serial,

If(Max(If(Type = 'Delrt', Date)) <= Max(If(Type = 'Del', Date)), Max(If(Type = 'Del', Date))) as Date

Resident Table

Group By Serial;