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..
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;
Try This:
TMP:
load * Inline [
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
];
Left Join
LOAD Type, Serial_no, Max(Date) as Date, '1' as This_is_the_Row Resident TMP
Where Type = 'Del'
Group by Type, Serial_no;
You have to use the column "This_is_the_Row "
Hi Swati,
Use this Expression
=aggr(max(Date),Serial_no,Dealer)
hope this will work.
By mistaken written wrong date in front of type: Delrt from Serial A3
1 | Delrt | 14/05/2017 | A3 | D03 |
Correct 1 is
1 | Delrt | 14/08/2017 | A3 | D03 |
tired but Didn't get any relevant answer by this .
Date | Qty | Dealer | Serial_no | This_is_the_Row | Type |
13/08/2017 | 1 | D03 | A3 | Del | |
13/05/2017 | 1 | D01 | A1 | Del | |
15/05/2017 | 1 | D01 | A1 | Delrt | |
14/08/2017 | 1 | D03 | A3 | Delrt | |
15/08/2017 | 1 | D04 | A3 | Del | |
16/08/2017 | 1 | D05 | A4 | Del | |
16/08/2017 | 1 | D05 | A4 | Delrt | |
17/08/2017 | 1 | D05 | A4 | Del | |
18/08/2017 | 1 | D06 | A5 | Del | |
19/08/2017 | 1 | D06 | A5 | Del | |
19/08/2017 | 1 | D06 | A5 | Delrt | |
12/08/2017 | 1 | D02 | A2 | 1 | Del |
I have tried with your data and the result is not what you show;
Send me the exact set of data....
I load your data and share you the result.
I want Serial ,dealer wise chart in front end with Max Del Date data to show the data to del cycle to user.
Date | Serial | Dealer |
12/08/2017 | A2 | D02 |
15/08/2017 | A3 | D03 |
17/08/2017 | A4 | D05 |
19/08/2017 | A5 | D06 |
I have used following expression
=aggr(max({<Type={'Del'}>}Date),Serial_no)
its work fine.
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 |
I checked but issue is in case of Serial No. A1, A3 .
I want when user select 2016 or 2016 with month 05 , he will not any data. he will get the data only when he select 2017 or 2017 with month 08 month.
Same with A3.
So I decided to work in backend n remove the entries in backend only.
Please check it
May be 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
];
Right Join (Table)
LOAD Dealer,
Serial,
Type,
Max(Date) as Date
Resident Table
Where Type = 'Del'
Group By Dealer, Serial, Type;