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

EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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

14 Replies
sunny_talwar

May be this

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

qv_testing
Specialist II
Specialist II

Nice Solution Sunny

MarcoWedel

Hi,

one solution could be also:

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

QlikCommunity_Thread_294595_Pic1.JPG

hope this helps

regards

Marco

Anonymous
Not applicable
Author

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

sunny_talwar

Should be like this

Left Join (...)

LOAD studentid,

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

Resident ....

Group By studentid;

sunny_talwar

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;

Capture.PNG

anagharao
Creator II
Creator II

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;

Anonymous
Not applicable
Author

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

Please check

sunny_talwar

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?