pick max value status

[Anonymous]

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

[cheenu_janakira]

Hi Raghav,

Why has the following not worked? Do you want to get Course Status in the table too?

LOAD

Std,

Prgrm,

Course,

Max(Date) as Max_Date

Resident Table 1

GROUP BY

Std,

Prgrm,

Course;

[YoussefBelloum]

because his date is not recongnized as date so you can't detect the max.

try to convert your "date" to a Date format before doing that

[cheenu_janakira]

Good eye. I didn't realise the answer was that obvious.

[VishalWaghole]

Try it,

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

];

NoConcatenate

Table2:

LOAD Std,

Prgrm,

Course,

Date(Max(NewDate)) as MaxDate

Resident Table1

Group by Std, Prgrm, Course;

drop Table Table1;

Regards,

Vishal Waghole

[VishalWaghole]

attachment for your referance

[YoussefBelloum]

before go to the attached app,

when you "Remove Course status to get max date for student,prgrm & course"

you will only get two lines as a result:

Std	Prgrm	Course	DATE
1	A	A1	27/11/17
2	B	B1	30/11/17

the code to format the date, group by and get the max date:

test:

LOAD Std, Prgrm,Course, [Course status],Date(MakeDate(right(date,2),num(month(date#(mid(date,3,3),'MMM'))),left(date,2)),'DD/MM/YY') as DATE

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

];

test1:

load distinct Std, Prgrm,Course, max(DATE) as DATE

resident test

Group by Std, Prgrm,Course;

DROP Table test;

see attached

[Anonymous]

Thank you all for your replies. plz ignore Date format.it was correctly showing up in my script.

What my requirement here is i need only max date status from table 1 to be picked up. for my code in table 2.

complete row data for max date. at each column stdname,program,course,status,....

[YoussefBelloum]

here it is another time with a group by on allt the columns:

test:

LOAD Std, Prgrm,Course, [Course status],Date(MakeDate(right(date,2),num(month(date#(mid(date,3,3),'MMM'))),left(date,2)),'DD/MM/YY') as DATE

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

];

NoConcatenate

test1:

load distinct Std, Prgrm,Course,[Course status], max(DATE) as DATE

resident test

Group by Std, Prgrm,Course,[Course status];

DROP Table test;

result:

Std	Prgrm	Course	Course status	DATE
1	A	A1	Cmplted	27/11/17
1	A	A1	Expired	26/11/17
2	B	B1	Expired	29/11/17
2	B	B1	Completed	26/11/17
2	B	B1	Enrolled	30/11/17

see attached

[Anonymous]

Hi Vishal, with your script i got below data. how ever i need status also for it. plz help.