14 Replies Latest reply: Mar 15, 2018 4:56 AM by Sunny Talwar

# 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"

thank you

• ###### Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

May be this

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

• ###### Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Nice Solution Sunny

• ###### 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

• ###### Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Should be like this

Left Join (...)

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

Resident ....

Group By studentid;

• ###### Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Here is a sample script

Table:

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)

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

Resident Table

Group By studentid;

• ###### Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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

• ###### 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?

• ###### Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

Left Join (Table)

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

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

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

• ###### Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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

• ###### Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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

Best,

Sunny

• ###### Re: EXPRESSION TO CHECK MULTIPLE RECORDS WITH DIFFERENT VALUES

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

Try

DATA:

[

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: