EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

[Anonymous]

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

[sunny_talwar]

May be this

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

[qv_testing]

Nice Solution Sunny

[MarcoWedel]

Hi,

one solution could be also:

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

hope this helps

regards

Marco

[Anonymous]

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;

[anagharao]

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]

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?