topic Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES in QlikView

EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all

I have below data in table and need a expression

To check if any one record for "result column" having passed value than finalresult is Promoted or failed

for student id

studentid sub result finalresult

1 s1 passed

1 s2 failed

1 s3 passed

2 s1 failed

2 s3 passed

3 s1 passed

3 s2 passed

3 s3 passed

4 s1 failed

4 s2 failed

4 s3 failed

Need below finalresult for each student ID

Studentid:1 ->>> atleast one subject passed hence finalresult "promoted"

2->>same as 1

3->> all subjects passed hence "promoted"

4->> final result:"Failed"

Please help to write expression to get above result

thank you

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

sunny_talwar — Tue, 13 Mar 2018 17:21:57 GMT

May be this

=If(MaxString(TOTAL <studentid> result) = 'passed', 'promoted', 'Failed')

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Nagaraju_KCS — Tue, 13 Mar 2018 18:42:46 GMT

Nice Solution Sunny

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

MarcoWedel — Tue, 13 Mar 2018 21:47:54 GMT

Hi,

one solution could be also:

If(Min(result='passed'),'promoted','failed')

hope this helps

regards

Marco

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Anonymous — Wed, 14 Mar 2018 10:36:52 GMT

Thank you sunny it's woking fine

How can I add same in script

I.e same expression tried in load statement by using group by but no luck

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

sunny_talwar — Wed, 14 Mar 2018 10:55:04 GMT

Should be like this

Left Join (...)

LOAD studentid,

If(MaxString(result) = 'passed', 'promoted', 'Failed') as finalresult

Resident ....

Group By studentid;

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

sunny_talwar — Wed, 14 Mar 2018 10:58:17 GMT

Here is a sample script

Table:

LOAD * INLINE [

studentid, sub, result

1, s1, passed

1, s2, failed

1, s3, passed

2, s1, failed

2, s3, passed

3, s1, passed

3, s2, passed

3, s3, passed

4, s1, failed

4, s2, failed

4, s3, failed

];

Left Join (Table)

LOAD studentid,

If(MaxString(result) = 'passed', 'promoted', 'Failed') as finalresult

Resident Table

Group By studentid;

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

anagharao — Wed, 14 Mar 2018 11:03:12 GMT

Try

DATA:

LOAD * INLINE

[

STUDENT_ID, SUB, RESULT

1,SUB1, PASS

1,SUB2, FAIL

1,SUB3, PASS

2,SUB1, PASS

2,SUB2, PASS

2,SUB3, PASS

3,SUB1, FAIL

3,SUB2, FAIL

3,SUB3, FAIL

];

LEFT JOIN

FINAL:

LOAD STUDENT_ID,

IF(WILDMATCH(CONCAT(RESULT,','),'*PASS*'),'PROMOTED','FAILED') AS FINAL_RESULT

RESIDENT DATA

GROUP BY STUDENT_ID;

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Anonymous — Wed, 14 Mar 2018 12:24:47 GMT

I tried but no luck always getting else result...I. E failed

Please check

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

sunny_talwar — Wed, 14 Mar 2018 12:26:04 GMT

Have you opened the qvw I have attached? I mean I see the right result from this sample script... would you be able to share a script which doesn't work?

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Anonymous — Wed, 14 Mar 2018 14:44:05 GMT

Left Join (Table)

LOAD studentid,

If(MaxString(result) = 'passed', 'promoted', 'Failed') as finalresult

Resident Table

Group By studentid;

Same script used added below two filter columns in if conditions but it's not working.Please check

Match (Location,'IND' ,'ITLY')and match( Diversity,'Asian','EUROPE')

Left Join (Table)

LOAD studentid,if (Match (Location,'IND' ,'ITLY')and match( Diversity,'Asian','EUROPE') AND

MaxString(result) = 'passed', 'promoted', 'Failed') as finalresult

Resident Table

Group By studentid;

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

sunny_talwar — Wed, 14 Mar 2018 14:49:26 GMT

You added other things to the script I gave... May be share your app or sample data with expected output to help you better

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Anonymous — Thu, 15 Mar 2018 05:17:21 GMT

Thank so much sunny

corrected my script and it is working fine

Except one corner scenaRio some of students are absent any one subject for them the result column updated as 'Not Present'...In my requirements if any subject absent also we need to change the finalResult to promoted.

In this case ,the student who's result have 'Not Present exam

MaxString(result) will return 'Not Present and final result will updated as failed

Please have a look

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Anonymous — Thu, 15 Mar 2018 05:36:34 GMT

Corrected string of result column it is 'Not present' it is

'Unknown-absent' for students who are absent

MaxString(result) will returned as 'Unknown absent' and final result updated as failed

Please have a look

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

sunny_talwar — Thu, 15 Mar 2018 08:56:51 GMT

Look where my friend? please provide a sample of what you are referring to.

Best,

Sunny