Pick max value status and blank values as well

[Anonymous]

Hi All,

Below is my data. I need higlited ones only in a single table.

I tried this.

T1:

Load Resource,Program,Course,Status,

from ...T1.xls

Below is my data.

Resource	Program	Course	Status	CourseDate
AB	A	A1	Enrolled	12/13/2017
AB	B	B1	Enrolled	12/17/2017
AB	A	A1	Completed	12/28/2017
AB	B	B1	Expired	01/02/2018
AB	C	C1	Enrolled
BC	A	A1	Enrolled	12/13/2017
BC	B	B1	Enrolled	12/17/2017
BC	A	A1	Expired	12/28/2017
BC	B	B1	Completed	01/02/2018

As shown i pic. I need only maxdate value status and blank values to be moved to a seperate table. please help/

Thanks,

Raghav

[tamilarasu]

Hi Raghav,

Try this,

T1:

Load Resource,Program,Course,Status, CourseDate

from ...T1.xls;

Inner Join (T1)

Load Resource, Program, Course, Max(CourseDate) as CourseDate

Resident T1

Group By Resource,Program,Course;

Concatenate (T1)

Load Resource,Program,Course,Status, CourseDate

from ...T1.xls where Len(Trim(CourseDate))=0;

[sumeet-vaidya]

Dear Raghav,

Kindly use below Script.

Test:

LOAD *,Resource&Program&Course AS Key Inline [

Resource, Program, Course, Status, CourseDate

AB, A, A1, Enrolled, 12/13/2017

AB, B,  B1, Enrolled, 12/17/2017

AB, A, A1, Completed, 12/28/2017

AB, B, B1, Expired, 01/02/2018

AB, C, C1, Enrolled,

BC, A, A1, Enrolled, 12/13/2017

BC, B, B1, Enrolled, 12/17/2017

BC, A, A1, Expired, 12/28/2017

BC, B,  B1, Completed, 01/02/2018

];

NoConcatenate

Test1:

LOAD

Resource&Program&Course AS Key,

Date(Max(Date#(CourseDate, 'MM/DD/YYYY')), 'MM/DD/YYYY') as Max_Date,

MaxString(Status) AS Final_Status,

'Max' as Flag

Resident Test

Group By Resource,Program,Course;

Final Ouput:  

Resource	Program	Course	Final_Status	Max_Date
AB	A	A1	Enrolled	12/28/2017
AB	B	B1	Expired	01-02-2018
AB	C	C1	Enrolled
BC	A	A1	Expired	12/28/2017
BC	B	B1	Enrolled	01-02-2018

Regards

Sumeet Vaidya

[shiveshsingh]

Date:LOAD * INLINE [
Resource, Program, Course, Status, CourseDate
AB, A, A1, Enrolled, 12/13/2017
AB, B, B1, Enrolled, 12/17/2017
AB, A, A1, Completed, 12/28/2017
AB, B, B1, Expired, 1/2/2018
AB, C, C1, Enrolled,
BC, A, A1, Enrolled, 12/13/2017
BC, B, B1, Enrolled, 12/17/2017
BC, A, A1, Expired, 12/28/2017
BC, B, B1, Completed, 1/2/2018
];

inner join(Date)

load max(CourseDate) as CourseDate,Resource, Program, Course
Resident Date
group by Resource, Program, Course;

Concatenate(Date)
load *
Resident Date where isnull(CourseDate);