Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

bsrravikumar
New Contributor III

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

Re: pick max value status

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;

27 Replies
cheenu_janakira
Contributor

Re: pick max value status

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
Esteemed Contributor

Re: pick max value status

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
Contributor

Re: pick max value status

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

vishalwaghole
Valued Contributor II

Re: pick max value status

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
Valued Contributor II

Re: pick max value status

attachment for your referance

YoussefBelloum
Esteemed Contributor

Re: pick max value status

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

bsrravikumar
New Contributor III

Re: pick max value status

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
Esteemed Contributor

Re: pick max value status

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


bsrravikumar
New Contributor III

Re: pick max value status

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

Untitled.jpg

Community Browser