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

[Anonymous]

Thank you All

[Anonymous]

hi sunny,

In my main app(table1) there are few rows with blank dates. i need them as well along with these max dates. If i do right join they are missing....

bcz based up them program status(one more new column) is to be derived

how to achive this. for blank date i gave like this If(isnull(date,1,0).as indicator.

[sunny_talwar]

May be like this

Table1:

LOAD *,

Date(Alt(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'), Today()),'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;

[Anonymous]

it is working. but what would this script do.

[sunny_talwar]

‌Alt function picks the first non-null value from the list of value provided. When Date is null, it will pick Today() as date. This helps in when you do the join.

Does that make sense?

[Anonymous]

ok. but what if i need the maxdate field to be blank instead of today() .

can i give this way?

Date(Alt(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'),'-'),'MM/DD/YYYY') as NewDate

this is bcz in my case enroll status should come only when course date is null

[sunny_talwar]

If you don't want to get rid of Null Dates, you can create a temporary date field for the purposes of join like this

Table1:

LOAD *,

Date(Alt(Date#(Mid(date,1,2)&'/'&Mid(date,3,3)&'/'&Mid(date,6,2),'DD/MMM/YY'), Today()),'MM/DD/YYYY') as NewDateTemp,

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(NewDateTemp)) as NewDateTemp

Resident Table1

Group by Std, Prgrm, Course;

The problem is that you cannot find a Max of '-' and that is why it won't work...

Alternatively, you can create a flag for null dates

If(Len(Trim(date)) = 0, 1, 0) as DateNullFlag

and then you can replace your NewDate nulls to be today's date

[Anonymous]

Thanks bro you have answer for every alternative