topic Re: Use Set Analysis into variable in QlikView

Use Set Analysis into variable

l_denivelle — Wed, 12 Aug 2015 10:07:55 GMT

I'm trying to put a variable into a Set Analysis. The problem is that my variable is the result of another Set Analysis.

Here an example :

I have my first Set Analysis :

LET MyFirstSetAnalysis = ''Avg( { < [Type de date]= { ' & chr(39)& 'Engagement' &chr(39)& '}, [Année]={'&chr(39)&'2015' &chr(39) & '} > } [Délai de réponse])';

Now I want a new Set Analysis which will contain the result of my first Set Analysis :

LET MySecondSetAnalysis= 'Count( { < [Délai] = { >= $(' & MyFirstSetAnalysis & ' ) } > } DISTINCT [Code module])';

If I display my variable I get this :

Count( { [Délai]= {>= } > } DISTINCT [Code Module];

Questions :

- Can we us Set Analysis into another Set Analysis ?

- If yes, how ?

Thank you !

Re: Use Set Analysis into variable

maximiliano_vel — Wed, 12 Aug 2015 11:35:44 GMT

The answer is yes, pls try this

LET MyFirstSetAnalysis = Avg( { < [Type de date]= {Engagement}, [Année]={2015} > } [Délai de réponse]);

LET MySecondSetAnalysis= Count( { < [Délai] = { ">= $(MyFirstSetAnalysis)" } > } DISTINCT [Code module])';

Re: Use Set Analysis into variable

sasiparupudi1 — Wed, 12 Aug 2015 11:37:01 GMT

You can not use $ sign in the varibles in the script

try using @ and replace it in the front end before the use

hth

Sasi

Re: Use Set Analysis into variable

rubenmarin — Wed, 12 Aug 2015 11:37:59 GMT

Hi, your first variable needs to start with an '='.

Your second variableneeds doublequotes for set analisys (don't use simple quotes or it will mess with your inserted variable).

It can work, I would first try it simplier trying if this works:

Count( { < [Délai] = {">= $(=Avg( { < [Type de date]= {'Engagement'}, [Année]={'2015'}> } [Délai de réponse]) )"} > } DISTINCT [Code module])

Re: Use Set Analysis into variable

Kushal_Chawda — Wed, 12 Aug 2015 11:39:23 GMT

may be try

Count( { < [Délai] = { >= '$(=MyFirstSetAnalysis))' } > } DISTINCT [Code module])

Re: Use Set Analysis into variable

jonathandienst — Wed, 12 Aug 2015 11:42:09 GMT

Is that a double quote or a pair of single quotes at the start of the first variable def? Should be one single quote:

LET MyFirstSetAnalysis = 'Avg( {<[Type de date]= {''Engagement''}, [Année]={2015}>} [Délai de réponse])';

(the embedded quotes are pairs of single quotes to escape the quotes. No need for chr(39))

The second is missing an = sign:

LET MySecondSetAnalysis= 'Count( {<[Délai] = {>=$' & '(=' & MyFirstSetAnalysis & ')}>} DISTINCT [Code module])';

(You need to split the $( to defer the expansion and be careful with spaces inside the set expression, some can break the expression)

Re: Use Set Analysis into variable

rubenmarin — Wed, 12 Aug 2015 11:43:50 GMT

Reading Sasidhar I remember than the Dollar will mess the result in script, you'll need to use CHR():

LET MyFirstSetAnalysis = ''=Avg( { < [Type de date]= { ' & chr(39)& 'Engagement' &chr(39)& '}, [Année]={'&chr(39)&'2015' &chr(39) & '} > } [Délai de réponse])';

Now I want a new Set Analysis which will contain the result of my first Set Analysis :

LET MySecondSetAnalysis= 'Count( { < [Délai] = {">= ' & Chr(36) & '(' & MyFirstSetAnalysis & '") } > } DISTINCT [Code module])';

Re: Use Set Analysis into variable

l_denivelle — Wed, 12 Aug 2015 12:49:54 GMT

hmm it doesn't work for me.

I can't use LET with your solution or maybe you forgot the quote ?

I tried with SET and I have the same empty result

Re: Use Set Analysis into variable

l_denivelle — Wed, 12 Aug 2015 12:53:58 GMT

I didn't understand well. Where exactly I have to put @ ?

I have to replace $ by @ ?

Re: Use Set Analysis into variable

sasiparupudi1 — Wed, 12 Aug 2015 12:58:46 GMT

Yes,use @ in the place of $ and replace it later when you want to use your variable

LET MySecondSetAnalysis= 'Count( { < [Délai] = { >= @(' & MyFirstSetAnalysis & ' ) } > } DISTINCT [Code module])';

replace(MySecondSetAnalysis,'@','$')

Re: Use Set Analysis into variable

maximiliano_vel — Wed, 12 Aug 2015 13:06:30 GMT

Try to create the variables in Settings->Variable Overview

Re: Use Set Analysis into variable

l_denivelle — Wed, 12 Aug 2015 13:07:08 GMT

It works !

but you forgot the double quote before and after the variable :

LET MySecondSetAnalysis= 'Count( {<[Délai] = {>=" $' & '(=' & MyFirstSetAnalysis & ')"}>} DISTINCT [Code module])';

thank you very much

Re: Use Set Analysis into variable

l_denivelle — Wed, 12 Aug 2015 13:09:35 GMT

Thank you all for your quick answers.

Jonathan's solution works for me.