topic Re: Getting a dynamic Match expression to work in QlikView

Getting a dynamic Match expression to work

Mon, 10 Mar 2014 09:43:05 GMT

Hi all!

I'm trying to create a pivot chart which uses a dynamic MATCH expression.

I've created a variable which changes based on users choice.

Inside the pivot the variable is used to decide which records to summarize.

However the expression in the pivot does not work.

Variable(vCondition):

=if(Group='Avdelingsopphold', chr(39) & 'E' & chr(39) &',' & chr(39) &'F' &',' & chr(39) & 'K' & chr(39) &',' & chr(39) & 'I' & chr(39), chr(39) & 'E' & chr(39) &',' & chr(39) &'S' &',' & chr(39) & 'K' & chr(39))

Expression:

=sum( if( match( Keyfield ,$(vCondition))>0,Amount,0) )

I'm using qv 11.20

Please find attached an example file.

Thanks,

Olav

Re: Getting a dynamic Match expression to work

Anonymous — Mon, 10 Mar 2014 15:34:50 GMT

Olav,

There is no attachment. For now, try this:

sum(if(match(Keyfield , vCondition), Amount, 0))

Regards,

Michael

Re: Getting a dynamic Match expression to work

Mon, 10 Mar 2014 15:41:34 GMT

... and here's the attachment

Re: Getting a dynamic Match expression to work

Mon, 10 Mar 2014 15:42:19 GMT

Hi Michael!

Thanks for replying, I've tried your solution with no luck....

Seems the attachment got messed up - will post it again.

Olav

Re: Getting a dynamic Match expression to work

marcus_sommer — Mon, 10 Mar 2014 15:48:24 GMT

It is quite difficult to create such lists without syntax trouble. You should use a additionally textbox/chart to see if your expressions returned exactly a string which looks like a manually string which successfully worked in your object.

Then you will need a $-sign expansion - have a look here: $-Sign Expansions in QlikView

May be it will look like:

=sum(if(match(Keyfield ,$(='$(vCondition)')), Amount))

- Marcus

Re: Getting a dynamic Match expression to work

Anonymous — Mon, 10 Mar 2014 15:54:04 GMT

I see... You can't use match here. Keyfield is a set of available values, not one value. My guess you want it to return true if at least one value in the Keyfield matches at least one letter in variable.

Is it correct?

Edit: It should work if one value is selected in the KeyField, but the variable is broken - there is no closing ' after F and after S. It should be

=if(Group='Avdelingsopphold', chr(39) & 'E' & chr(39) &',' & chr(39) &'F' & chr(39) &',' & chr(39) & 'K' & chr(39) &',' & chr(39) & 'I' & chr(39), chr(39) & 'E' & chr(39) &',' & chr(39) &'S' & chr(39)&',' & chr(39) & 'K' & chr(39))

Re: Getting a dynamic Match expression to work

Tue, 11 Mar 2014 08:31:52 GMT

Hi Marcus!

Still does not work.

I've tried the complete expression as a variable as well, not only part of it.

The expression is working without the variable.

Am I blind to something here?

Olav

Re: Re: Getting a dynamic Match expression to work

marcus_sommer — Tue, 11 Mar 2014 10:35:25 GMT

Hi Olav,

you have had a small error within your variable. To avoid this you should write your expressions with a little bit more structure - have a look on the attachment.

- Marcus

Re: Getting a dynamic Match expression to work

Tue, 11 Mar 2014 10:52:24 GMT

thanks, works perfect.

Michael got the right answer too , strange I didn't see that, structuring is important

Re: Getting a dynamic Match expression to work

Anonymous — Tue, 11 Mar 2014 12:01:34 GMT

I'm sure you didn't see it because I used "edit". Apparently notifications are not sent on edit.

The important part that you got the solution.