topic Re: Exists Lookup in QlikView

Exists Lookup

Fri, 08 Jan 2016 14:04:15 GMT

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

Re: Exists Lookup

sunny_talwar — Fri, 08 Jan 2016 18:19:54 GMT

May be this in a straight table:

Dimensions

1) AccountCode

2) QuestionType

Expression:

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

Re: Exists Lookup

petter — Fri, 08 Jan 2016 18:22:04 GMT

Dimension: AccountCode

Two Expressions:

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

='N/A'

Re: Exists Lookup

Gysbert_Wassenaar — Fri, 08 Jan 2016 18:36:27 GMT

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

Re: Exists Lookup

Fri, 08 Jan 2016 20:31:19 GMT

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?

Re: Exists Lookup

Peter_Cammaert — Fri, 08 Jan 2016 21:19:44 GMT

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

same dimensions, also extensible on both counts

Re: Exists Lookup

Fri, 08 Jan 2016 21:30:25 GMT

Thank you

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

Re: Exists Lookup

Peter_Cammaert — Fri, 08 Jan 2016 22:07:52 GMT

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.

Re: Exists Lookup

petter — Sun, 10 Jan 2016 08:51:30 GMT

Please mark the thread as answered.