topic Re: pick max value status in QlikView

pick max value status

Anonymous — Thu, 30 Nov 2017 15:32:00 GMT

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

Re: pick max value status

cheenu_janakira — Thu, 30 Nov 2017 15:45:58 GMT

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;

Re: pick max value status

YoussefBelloum — Thu, 30 Nov 2017 15:47:37 GMT

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

Re: pick max value status

cheenu_janakira — Thu, 30 Nov 2017 15:53:11 GMT

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

Re: pick max value status

VishalWaghole — Thu, 30 Nov 2017 16:10:20 GMT

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

Re: pick max value status

VishalWaghole — Thu, 30 Nov 2017 16:16:46 GMT

attachment for your referance

Re: pick max value status

YoussefBelloum — Thu, 30 Nov 2017 16:18:12 GMT

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

Re: pick max value status

Anonymous — Thu, 30 Nov 2017 16:19:18 GMT

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,....

Re: pick max value status

YoussefBelloum — Thu, 30 Nov 2017 16:23:36 GMT

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

Re: pick max value status

Anonymous — Thu, 30 Nov 2017 16:28:21 GMT

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

Re: pick max value status

sunny_talwar — Thu, 30 Nov 2017 16:32:47 GMT

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

Re: pick max value status

Anonymous — Thu, 30 Nov 2017 16:34:34 GMT

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

Re: pick max value status

cheenu_janakira — Thu, 30 Nov 2017 16:36:30 GMT

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;

Re: pick max value status

YoussefBelloum — Thu, 30 Nov 2017 16:38:54 GMT

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

Completed

26/11/17

Expired

29/11/17

so for the combination

you will have three lines:

2	B	B1	Expired	29/11/17
2	B	B1	Completed	26/11/17
2	B	B1	Enrolled	30/11/17

Re: pick max value status

Anonymous — Thu, 30 Nov 2017 16:40:56 GMT

WITH right join i got below data.

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;

Re: pick max value status

cheenu_janakira — Thu, 30 Nov 2017 16:42:59 GMT

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?

Re: pick max value status

sunny_talwar — Thu, 30 Nov 2017 16:44:21 GMT

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

Re: pick max value status

sunny_talwar — Thu, 30 Nov 2017 16:45:07 GMT

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;

Re: pick max value status

cheenu_janakira — Thu, 30 Nov 2017 16:49:32 GMT

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.

Re: pick max value status

Anonymous — Thu, 30 Nov 2017 16:51:23 GMT

Thank you Sunny