topic Re: help to convert if-statement into set analysis in QlikView

help to convert if-statement into set analysis

Wed, 26 Mar 2014 19:00:14 GMT

Hello

this should be fairly easy for an experienced user, but has caused me some issues today.

I have the following if-statement ( returns the correct value):

=count (if ( right (SourceCode_Auto,4) = '7932' or right (SourceCode_Auto, 4) = '7966', [Interest_IdCount]))

i.e. I have 2 conditions and one has to be fulfilled for the count to kick in. The SourceCode field is of the format xxxNNNNN, minimum 4 digits/letters and maximum8.The condition needs to be on the 4 last ones.

now, I tried this set analysis ( returns a too low value

=count ( { $ < [SourceCode_Auto] = {'*7932', '*7966'} > [Interest_IdCount] }

Why doesn't that work out, and how should it be written?

many thanks to the person that might take pity on a beginner and explain this mystery.

Re: help to convert if-statement into set analysis

jerem1234 — Wed, 26 Mar 2014 19:06:27 GMT

Should work, but looks like your syntax is a little off:

=count ( { $ < [SourceCode_Auto] = {'*7932', '*7966'} >} [Interest_IdCount] )

Hope this helps!

Re: help to convert if-statement into set analysis

maxgro — Wed, 26 Mar 2014 20:05:55 GMT

=count ( { $ < [SourceCode_Auto] = {'*7932', '*7966'} >} [Interest_IdCount] )

1) no set analysis

=count ( [Interest_IdCount] )

2) set analysis

=count ( { $ } [Interest_IdCount] )

3) add modifiers

=count ( { $ < [SourceCode_Auto] = {'*7932', '*7966'} > } [Interest_IdCount] )

google this

set analysis walter wizard for qlikview

thanks to Stefan WALTHER

Re: help to convert if-statement into set analysis

Thu, 27 Mar 2014 07:43:53 GMT

good morning - and thanks for taking the time!

for some reason I wasn't able to copy paste the code into the original question, instead I typed it... and when I did I just simply missed that last curly bracket, I did have it in the original statement just as you and Massimo proposed. I guess otherwise I wouldn't even get a numerical answer.

so I guess the question is still up for answering, why does the if-statement produce a correct answer, but not the set analysis?

Re: help to convert if-statement into set analysis

Thu, 27 Mar 2014 07:44:58 GMT

thanks! I will google that! however, the set analysis still doesn't work... I will continue the quest today. thanks anyway!

Re: help to convert if-statement into set analysis

maxgro — Thu, 27 Mar 2014 08:29:58 GMT

if you can, post your qvw, perhaps the community can helpyou

Re: help to convert if-statement into set analysis

Thu, 27 Mar 2014 09:04:45 GMT

hmmm. If I make the count distinct, the expressions return the same value.

otherwise I guess I should maybe create a field that I summarize instead, that might work.

anyway, thanks for your input.

ps. if you have the time, what would be in parameters to enter into the wizard to create this statement? I struggled with creating the "or" in the condition.

Re: help to convert if-statement into set analysis

Thu, 27 Mar 2014 10:11:33 GMT

Hi there,

On top of what jerem and Massimo have said..

Assuming Interest_IdCount = 1 where Interest_Id is a unique entry i.e. in your script it could be 1 As Interest_IdCount

Instead of

=count ( { $ < [SourceCode_Auto] = {'*7932', '*7966'} >} [Interest_IdCount] )

=Sum ( { $ < [SourceCode_Auto] = {'*7932', '*7966'} >} [Interest_IdCount] )

also checking if you have any current selections/ filters that could affect the numbers you expect.

Re: help to convert if-statement into set analysis

Thu, 27 Mar 2014 10:47:50 GMT

=count (distinct( if( PartnerCode='Facebook*' or left(Etag,2)='FB' or left(Etag,2)='fb' or right(SourceCode_Auto,5)= '07932' or right(SourceCode_Auto,5)= '07966' ,[Interest_IdCount])))

having managed to get the ifs and the setanalysis returning the same results, here is the next problem. I want to add conditions not only to the one field, but to several. again, it's enough that one of them is true for the count to happen.

=count({$ < [SourceCode_Auto] = {'*7932','*7966'}, [PartnerCode]={'Facebook*'}, [Etag]={'FB*', 'fb'}>} distinct [Interest_IdCount])

returns - of course - only the intersection of the three fields. how do I make it "or" instead of "and"?

Re: help to convert if-statement into set analysis

Thu, 27 Mar 2014 11:38:53 GMT

To get [SourceCode_Auto] = {'*7932','*7966'} or [PartnerCode]={'Facebook*'}> or [Etag]={'FB*', 'fb'} you weould want to use the Set Operator Union +. The following should hopefully work.

=count({$ < [SourceCode_Auto] = {'*7932','*7966'}> + < [PartnerCode]={'Facebook*'}> + <[Etag]={'FB*', 'fb'}>} distinct [Interest_IdCount])

or if you were especiall interested in the SourceCode_Auto field

=count({$ < [SourceCode_Auto] = {'*7932','*7966'}, [PartnerCode]={'Facebook*'}> + < [SourceCode_Auto] = {'*7932','*7966'}, [Etag]={'FB*', 'fb'}>} distinct [Interest_IdCount])

Re: help to convert if-statement into set analysis

maxgro — Thu, 27 Mar 2014 11:53:02 GMT

here for more on set analysis

Sets Analysis: syntaxes, exemples

Re: help to convert if-statement into set analysis

Thu, 27 Mar 2014 11:53:54 GMT

and it does! thank you so much!

Re: help to convert if-statement into set analysis

Thu, 27 Mar 2014 11:59:48 GMT

merci beaucoup!