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
Check your date format it might be a string value. Convert to date format and apply your logic.
One way to convert string is Date(Date#(Date1,'dd/m/yyyy'),'DD/MM/YYYY')
This is not Date Format Issue. This is related to the Logic.
Hi,
I can suggest you to separate your table on 2 tables, because of extreme values are in different rows.
I would made first table with Key and Max(Date1), second table with Key and Max(Date2) and afterwards join tables or use ApplyMap()
Hope it helps.
Andrei
Use this script
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
];
Left Join
B:
Load
Key,
Date(Max(Date#(Date1,'DD/MM/YYYY')),'DD/MM/YYYY') as MaxDate1,
Date(Max(Date#(Date2,'DD/MM/YYYY'),2),'DD/MM/YYYY') as MaxDate2
Resident A
Group By Key;
Try this one it will work
TableA:
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
];
//NoConcatenate
//LOAD max(Date1) as DAte3,Key Resident TableA Group by Key;
load MinString(Date1) as Date1,Key
Resident TableA
group by Key,1;
drop table TableA;
Use a straight table;
dimension: Key
expression1: Max(Date1)
expression2: Max(Date2)
Thanks for your reply.
I need Max(date,2) As per the Date1. Youe Logic doesn't give me desired result. Kindly see the output required in the top post.
Hi Tresesco Sir
I actually want this:
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
Max(Date2) wont give me this
What is the logic in your result?
I mean you need to find max(Date1) for every Key
and how(?) or what(?) do you want to find with Date2 ???