Exists Lookup

[#1]

Good day,

I have a table 'transactions' that has 3 columns:

AccountCode    QuestionType    Questionans

111111               pep                         No

222222               pep                         Yes

333333               pep                          N/A

and another table 'Valid Response' that has 2 columns

QuestionType    ans

pep                     No

pep                     Yes

I have a straight table where I would like to display transaction table items where there is no valid entry in the Valid Response table.

i.e. 333333   pep     N/A

What would be the expression that I would use in chart properties?

Thank you very much

[sunny_talwar]

May be this in a straight table:

Dimensions

1) AccountCode

2) QuestionType

Expression:

=Only({<Questionans = {'N/A'}>}Questionans)

[petter]

Dimension: AccountCode

Two Expressions:

=FirstSortedValue( {<Questionans={'N/A'}>} QuestionType , AccountCode )

='N/A'

[Gysbert_Wassenaar]

Try =If(index(concat(ans,'|'),Questionans) ,null(),Questionans) as expression with AccountCode and QuestionType as dimensions.

[#5]

This worked great and is extensible. Thank you very much.

If the  'transactions' table had one more row:

AccountCode    QuestionType    Questionans

111111               loc                            frd

and items with QuestionType  = 'loc'  should be excluded from the lookup.

How would that change the expression?

[Peter_Cammaert]

=If (match(QuestionType, 'loc') or index(concat(ans,'|'),Questionans) ,null(), Questionans)

same dimensions, also extensible on both counts

[#7]

Thank you

One more please, What if I have multiple  items like 'loc'?

[Peter_Cammaert]

If you want to skip more QuestionTypes, add their QuestionType value to the match() call as additional parameter(s).

Check the match() documentation in 'Conditional Functions' in QV Desktop Help.

[petter]

Please mark the thread as answered.