Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
Below is my scenario
Table1:
Std, Prgrm,Course, Course status,date,....,.....
1,A,A1,Cmplted,27Nov17,....,.....
1,A,A1,Expired,26Nov17,....,.....
2,B,B1,Completed,26Nov17,....,.....
2,B,B1,Expired,29Nov17,....,.....
2,B,B1,Expired,26Nov17,....,.....
2,B,B1,Enrolled,30Nov17,....,.....
Table2
//(Resident Load from Table1 ,Removed Coursestatus to get max date for student,prgrm & course )
//this is how i need data for Table1.
Std, Prgrm,Course, date
1,A,A1,27Nov17
2,B,B1,29Nov17
2,B,B1,30Nov17
Now how can i get max max date status implied to main table?
Thanks,
Raghav
Why don't you just do this
Table:
LOAD Std,
Prgrm,
Course,
Course status,
date
FROM ....;
Right Join (Table)
LOAD Std,
Prgrm,
Course
Max(date) as date
Resident Table
Group By Std, Prgrm, Course;
Basically Right Join will make sure to keep only those rows in Table where the Date is Max(date) grouped by Std, Prgrm, Course
In my final table I need only below data as they have maxdate with final status. Plz let me know how to achieve this.
result:
Std | Prgrm | Course | Course status | DATE |
1 | A | A1 | Cmplted | 27/11/17 |
2 | B | B1 | Enrolled | 30/11/17 |
Hi Raghav,
If you want to add Course status, I would try the following. However, it will only work if all Corse Statuses have different and incrementing dates. Otherwise, if there is a Enrolled and Completed sequence that are on same date, this would not work. If this is the case, try "numbering" the statuses with a Dual function and "maxing" on that. please feel free to give more detdta information.
Table2:
LOAD
*,
Std & '_' & Prgrm & '_' & Course & Max_Data as FK01;
LOAD
Std,
Prgrm,
Course,
Max(Date) as Max_Date
Resident Table 1
GROUP BY
Std,
Prgrm,
Course;
LEFT JOIN(Table2)
LOAD
Std & '_' & Prgrm & '_' & Course & Max_Data as FK01,
[Course Status]
RESIDENT Table1;
You can't, because when you add "Course status" on the table, you can't ignore these lines:
2 | B | B1 | Completed | 26/11/17 |
2 | B | B1 | Expired | 29/11/17 |
so for the combination
2 | B | B1 |
you will have three lines:
2 | B | B1 | Expired | 29/11/17 |
2 | B | B1 | Completed | 26/11/17 |
2 | B | B1 | Enrolled | 30/11/17 |
WITH right join i got below data.
Table1:
LOAD *,
Date(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'),'MM/DD/YYYY') as NewDate
Inline [
Std, Prgrm,Course, Course status,date
1,A,A1,Cmplted,27Nov17
1,A,A1,Expired,26Nov17
2,B,B1,Completed,26Nov17
2,B,B1,Expired,29Nov17
2,B,B1,Expired,26Nov17
2,B,B1,Enrolled,30Nov17
];
Right Join
Table2:
LOAD Std,
Prgrm,
Course,
Date(Max(NewDate)) as MaxDate
Resident Table1
Group by Std, Prgrm, Course;
One mistake in code. Try this:
Table2:
LOAD
*,
Std & '_' & Prgrm & '_' & Course & Max_Date as FK01;
LOAD
Std,
Prgrm,
Course,
Max(Date) as Max_Date
Resident Table 1
GROUP BY
Std,
Prgrm,
Course;
LEFT JOIN(Table2)
LOAD
Std & '_' & Prgrm & '_' & Course & Date as FK01,
[Course Status]
RESIDENT Table1;
Ideally, you have date/time, rather than date. As explained before, if 2 statuses occur on same date, this won't work. In this case, I would do an inline mapping load for giving numerical values to the status, then try the same technique as above but then the 2nd table using a "max" on the field that contains 'mapped status number'.
Can you share exact script for editing and replying to you?
Don't rename Max(Date) to MaxDate... keep the same name, so that you right join on Date also
Like this
Table1:
LOAD *,
Date(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'),'MM/DD/YYYY') as NewDate
Inline [
Std, Prgrm,Course, Course status,date
1,A,A1,Cmplted,27Nov17
1,A,A1,Expired,26Nov17
2,B,B1,Completed,26Nov17
2,B,B1,Expired,29Nov17
2,B,B1,Expired,26Nov17
2,B,B1,Enrolled,30Nov17
];
Right Join
Table2:
LOAD Std,
Prgrm,
Course,
Date(Max(NewDate)) as NewDate
Resident Table1
Group by Std, Prgrm, Course;
The point is to first find the max date and then a logic to find the "max status" that you are looking for. Then do a join on a concatenated field of all the other fields to find the right corresponding "status" to its record.
Hope that makes sense.
Thank you Sunny