Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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

27 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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

cheenu_janakira
Creator III
Creator III

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;

YoussefBelloum
Champion
Champion

You can't, because when you add "Course status" on the table, you can't ignore these lines:

2BB1Completed26/11/17
2BB1Expired29/11/17

so for the combination

2

B

B1

you will have three lines:

2BB1Expired29/11/17
2BB1Completed26/11/17
2BB1Enrolled30/11/17
Anonymous
Not applicable
Author

WITH right join i got below data.

right join.jpg

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;

cheenu_janakira
Creator III
Creator III

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?

sunny_talwar

Don't rename Max(Date) to MaxDate... keep the same name, so that you right join on Date also

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;

cheenu_janakira
Creator III
Creator III

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.

Anonymous
Not applicable
Author

Thank you Sunny