Set analysis

[#1]

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

[Anonymous]

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

[#3]

Hi,

no proble.

I'm using QV 11.

The chart type is a pivot table.

[Anonymous]

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

[#5]

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

[Anonymous]

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

[#7]

I'll try.

Thanks again

Regards

Giampiero

[#8]

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

[Anonymous]

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.

[#10]

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