Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

kalyanqlik15
New Contributor II

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

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

May be this

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

qv_testing
Valued Contributor

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Nice Solution Sunny

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Hi,

one solution could be also:

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

QlikCommunity_Thread_294595_Pic1.JPG

hope this helps

regards

Marco

kalyanqlik15
New Contributor II

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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

MVP
MVP

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Should be like this

Left Join (...)

LOAD studentid,

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

Resident ....

Group By studentid;

MVP
MVP

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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;

kalyanqlik15
New Contributor II

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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

Please check

MVP
MVP

Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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?

Community Browser