Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community
I have one issue:
Load * Inline [
Key, Date1 , Date2
AAA, 21/3/2014 , 4/4/2014
AAA, 22/3/2014 , 10/4/2014
AAA, 24/3/2014 , 8/4/2014
BBB, 20/3/2014 , 15/4/2014
BBB, 25/3/2014 , 17/4/2014
];
Desired Output Required:
Key Date1 Date2
AAA 24/3/2014 10/4/2014
BBB 25/3/2014 15/4/2014
Need Suggestions
Regards
Aviral Nag
PFA the output u asked is attached
To pick Second Last Date2 on the basis of Max Date1
The Date2 coming against the Max Date1, I need its above date.
hey check this..
Now I got your logic. Try this expression for Date2:
=FirstSortedValue(Date2,-Date1, 2)
Dimension: Key
see the modified one
A:
Load * Inline [
Key, Date1, Date2
AAA, 21/3/2014, 4/4/2014
AAA, 22/3/2014, 10/4/2014
AAA, 24/3/2014, 8/4/2014
BBB, 20/3/2014, 15/4/2014
BBB, 25/3/2014, 17/4/2014
];
B:
Load
Key,
Date(Max(Date#(Date1,'DD/MM/YYYY')),'DD/MM/YYYY') as MaxDate1
Resident A
Group By Key;
C:
Load *
where MaxDate2 <> Null();
NoConcatenate
Load
Key,
If(Key='AAA',Date(Max(Date#(Date2,'DD/MM/YYYY')),'DD/MM/YYYY')) as MaxDate2
Resident A
Group By Key;
😧
Load *
where MaxDate2 <> Null();
NoConcatenate
Load
Key,
If(Key='BBB',Date(Min(Date#(Date2,'DD/MM/YYYY')),'DD/MM/YYYY')) as MaxDate2
Resident A
Group By Key;