Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

pick max value status

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

27 Replies
cheenu_janakira
Creator III
Creator III

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
Champion
Champion

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
Creator III
Creator III

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

VishalWaghole
Specialist II
Specialist II

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
Specialist II
Specialist II

attachment for your referance

YoussefBelloum
Champion
Champion

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
1AA127/11/17
2BB130/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
Not applicable
Author

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
Champion
Champion

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
1AA1Cmplted27/11/17
1AA1Expired26/11/17
2BB1Expired29/11/17
2BB1Completed26/11/17
2BB1Enrolled30/11/17


see attached


Anonymous
Not applicable
Author

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

Untitled.jpg