Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

help to convert if-statement into set analysis

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.

12 Replies
jerem1234
Specialist II
Specialist II

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

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


Hope this helps!

maxgro
MVP
MVP


=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

Not applicable
Author

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?

Not applicable
Author

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

maxgro
MVP
MVP

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

Not applicable
Author

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.

Not applicable
Author

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

do

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

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

Not applicable
Author

=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"?

Not applicable
Author

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