Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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
What is not right about the output you see in the attached file above?
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)
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?
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.
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
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..:(
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;
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..
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;