Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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);