topic Set analysis in QlikView

Set analysis

Tue, 18 Dec 2012 11:15:04 GMT

Hi everybody,

I have some problem with an expression.

At the moment I have this espression (in my pivot table) that works perfectly.

if(GetFieldSelections(Text) = 'Escludi'
,//THEN
sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) },Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}>} Partite.WRBTR)
,//ELSE
sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> } Partite.WRBTR)
)

Unfortunately, sometimes I have some problem with the memory of my object (pivot table).

I know that IF statement is not so good for performance so, I'm trying to put my IF in the set analysis.

I've just tried this way:

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( =if(GetFieldSelections(Text) = 'Escludi', Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"} ) ) } >} Partite.WRBTR)

No error are mensioned but it doesn't show what I'm expecting to have (the result is ZERO on every row of my table).

Any suggestion to improve my expression ?

Am I well imported the IF in the set analysis ?

Thanks in advance

Re: Set analysis

Anonymous — Tue, 18 Dec 2012 11:43:56 GMT

Hi,

I think there is an error in the 'THEN' sum expression.

Try this:

if(GetFieldSelections(Text) = 'Escludi'
,//THEN
sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) },Partite.FCONTO= {"*"}-{"G*"}>} Partite.WRBTR)
,//ELSE
sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> } Partite.WRBTR)
)

Cheers!

Janusz

Re: Set analysis

Tue, 18 Dec 2012 12:53:56 GMT

Hi Janusz,

thanks.

At the moment this expression

works perfectly.

What I'm to do is change that expression in this one:

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( =if(GetFieldSelections(Text) = 'Escludi', Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"} ) ) } >} Partite.WRBTR)

Unfortunately it doesn't work.

I'm trying to understand why.

Thanks a lot

Re: Set analysis

Anonymous — Tue, 18 Dec 2012 13:39:33 GMT

Hi,

I'm not sure why you want to do it this way, but this is your call.

Try this:

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( $(=if(GetFieldSelections(Text) = 'Escludi', 'Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}'))) } >} Partite.WRBTR)

Regards,

Janusz

Re: Set analysis

Tue, 18 Dec 2012 13:50:44 GMT

Hi,

unfortunately it doesn't work. The result is still zero.

About the other expression.

Your way:

it's very interesting. THANKS.

I'm trying to change this way to write the expression because I have some performance problem.

I know that eliminating if statement you can improve the performance.

THANK YOU very much for your help.

I'm really appreciating.

Re: Set analysis

Anonymous — Tue, 18 Dec 2012 13:57:51 GMT

Hi,

By removing the if statement from the aggregation expression gives you performance improvement.

In your case it will change nothing. There must be something else.

First, I would check the data model to make sure that it has been created according to the 'best practices'. You can search community for more info.

Regards,

Janusz

Re: Set analysis

Tue, 18 Dec 2012 14:07:47 GMT

I see.

Thank you Janusz.

My thread is been created because of my performance problem.

That expression is on a pivot table that often give me the message "out of memory".

I was trying to resolve this problem.

I have only three tables on my data model and the biggest table have 3.6 millions of records.

Thank you so much for your kindness.

best regards

Giampiero

Re: Set analysis

Wed, 19 Dec 2012 10:00:55 GMT

Hi Janusz,

I made a simple test to understand my performance problem.

As you remember I have this expression:

It works perfectly but I have serious performance problem.

I thought the problem was my if statement, so i tried to eliminate the IF and i was trying this expression:

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( $(=if(GetFieldSelections(Text) = 'Escludi', 'Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}'))) } >} Partite.WRBTR)

Unfortunately it doesn't work.

Just to be sure it's the IF statement the problem, yesterday I chenged my expression writing only:

sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> } Partite.WRBTR)

Now I don't have any more performance problem.

I can't use this expression and I have to make right this one

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( $(=if(GetFieldSelections(Text) = 'Escludi', 'Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}'))) } >} Partite.WRBTR)

Have you some suggestion to help me ?

Thank you so much

Giampiero

Re: Set analysis

Anonymous — Wed, 19 Dec 2012 10:18:00 GMT

Hi,

Did you try to replace your expression with the one from my first reply?

Do you still have a performance issue?

Regards,

Janusz

Re: Set analysis

Wed, 19 Dec 2012 10:20:14 GMT

Hi,

Yes i did it.

Nothing changed.

regards

Giampiero

Re: Set analysis

Anonymous — Wed, 19 Dec 2012 10:34:57 GMT

Hi,

Would you post the Table Viewer screenshot so I can understand more your data model, please.

What is the chart type that you execute the expression for?

Are you using the QV 11?

Regards,

Janusz

Re: Set analysis

Wed, 19 Dec 2012 10:53:11 GMT

Hi,

no proble.

I'm using QV 11.

The chart type is a pivot table.

Re: Set analysis

Anonymous — Wed, 19 Dec 2012 12:49:49 GMT

Hi,

The Partite.FCONTO field is a key field, and the best practise is not to use the key fields in the calculation expressions.

I understand that the Partite.FCONTO is a text field, so I suggest to keep it as it is in the Partite table, and create a new field just for the linkage purpose. I suggest to use the autonumberhash128 function to create a key between those two tables. Please, search the community for the best practices of the data modelling in QlikView.

Regards,

Janusz

Re: Set analysis

Wed, 19 Dec 2012 12:58:12 GMT

Hi Janusz,

THANK YOU so much.

I really appreciated your help.

I'll try to follow your way for those two tables.

At the same time, I'll try to solve the problem of the expression.

If you have any idea about it, I'll be grateful for the help.

Best regards

Giampiero

Re: Set analysis

Anonymous — Wed, 19 Dec 2012 13:05:43 GMT

Hi,

I believe your issue is strictly related to the fact that this field is a key field.

Changing that should do the work.

Regards,

Janusz

Re: Set analysis

Wed, 19 Dec 2012 13:09:34 GMT

I'll try.

Thanks again

Regards

Giampiero

Re: Set analysis

Wed, 19 Dec 2012 13:34:57 GMT

Hi,

I did it.

I changed my expression as above:

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO = {$(=if(GetFieldSelections(Text) = 'Escludi' , '"*"}-{"G*"', '"*"'))} >} Partite.WRBTR)

It works and my performances are much better now.

Now, I'm going to change the key as you told me.

THANK YOU so much for your help.

Best Regards

Giampiero

Re: Set analysis

Anonymous — Wed, 19 Dec 2012 14:50:47 GMT

Hi Giampiero,

You really overcomplicate that.

I've already written that the main problem is with the fact that you've used the key field in the expression.

So, you need to change that, and use your original expression (with my small change) and you should be fine:

if(GetFieldSelections(Text) = 'Escludi'
,//THEN
sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear)},Partite.FCONTO= {"*"}-{"G*"}>}Partite.WRBTR)
,//ELSE
sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> }Partite.WRBTR)
)

Once again, the priority is to change the data model first!!!

Regards,

Janusz

BTW. I don't believe that your last reply should be actually marked as an answer to this conversation.

Re: Set analysis

Wed, 19 Dec 2012 15:14:10 GMT

Hi Janusz,

I understand what you're saying about the solution.

I understand that the best way is to change the data model as you told me.

I still have to try your solution to see if it can give me the same results ... our customer is not so patient 🙂

I really don't want to mark my last reply to have point but to help other people.

For that reason I marked "OK" my last reply.

So, you're right when you say that, but I'd like to show to someonelse that this thread could be useful to give help with IF statement in SETANALYSIS (and I thought the best way it was to mark the thread as solved).

I say again, THANK YOU so much for your precious help.

I'll follow your suggestion with data model.

Best regards

Giampiero