Logic

[aveeeeeee7en]

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

[nizamsha]

PFA  the output u asked is attached

[aveeeeeee7en]

To pick Second Last Date2 on the basis of Max Date1

The Date2 coming against the Max Date1,  I need its above date.

[#4]

hey check this..

[tresesco]

Now I got your logic. Try this expression for Date2:

=FirstSortedValue(Date2,-Date1, 2)

Dimension: Key

[#6]

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;