Getting a dynamic Match expression to work

[#1]

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

[Anonymous]

Olav,

There is no attachment.  For now, try this:

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

Regards,

Michael

[#3]

... and here's the attachment

[#4]

Hi Michael!

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

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

Olav

[marcus_sommer]

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

[Anonymous]

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))

.

[#7]

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

[marcus_sommer · Accepted solution]

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

[#9]

thanks, works perfect.

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

[Anonymous]

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.