topic Re: Dynamic Set Analysis in QlikView

Dynamic Set Analysis

richard_pearce6 — Tue, 10 Sep 2013 13:50:47 GMT

Hi all,

I'm having some fun with some Dynamic Set Analysis. The calculation is basic and works as shown below:

CalcID = 1234 (the user will only select one CalcID at a time and the results are in a text box)

=sum( {<FieldA= {AAAA,BBBB}

,FieldB={A123,B123,C123}

,FieldC={'ZZZZ'}

,FieldD={999999}

,FieldE=P({1<LookUpField={'Look Up Value'}>} ReturnFields )

>} Sum_AMT)

The added level of complexity is Fields may not be used in all calculations. What drives the Fields I need to include and their conditions is a number of Meta tables with CalcID as the primary key.

The table will look something like this:

CalcID FieldName Value

1234 FieldA AAAA

1234 FieldA BBBB

1234 FieldB A123

1234 FieldB B123

Etc......

So first I need to know if FieldA exists

if it doesn't I either need to exclude it from the calculation or set it to "*"

or If the fields does exist I need to set the where clause

Can anyone help? I'm going down a dark route of nested variables and wondered if there's a cleaner solution to the problem?

Many thanks

Rich

Re: Dynamic Set Analysis

Tue, 10 Sep 2013 13:56:09 GMT

If(FieldName = '' or null(),'*', sum(yourexpression))

Re: Dynamic Set Analysis

richard_pearce6 — Tue, 10 Sep 2013 14:04:30 GMT

Thanks Thomas,

Maybe I'm not understanding your response correctly

I tested conditional statements original expression so it would be something like this:

=sum( {< If(FieldA=True,FieldA= {AAAA,BBBB},FieldA=)

,FieldB={A123,B123,C123}

,FieldC={'ZZZZ'}

,FieldD={999999}

,FieldE=P({1<LookUpField={'Look Up Value'}>} ReturnFields )

>} Sum_AMT)

This is an example, I'd have to use conditional statements on all the fields in the final expression although QV doesn't seem to like conditional statements within Set Analysis

Rich

Re: Dynamic Set Analysis

Tue, 10 Sep 2013 14:08:30 GMT

Well, im not sure i understanded your problem correctly

You need to check if FieldName is empty right? If it is empty FieldName should be * and value will be null() right?

I think i would do something like this:

=if(FieldName = null() or '', '*',

sum( {<FieldA= {AAAA,BBBB}

,FieldB={A123,B123,C123}

,FieldC={'ZZZZ'}

,FieldD={999999}

,FieldE=P({1<LookUpField={'Look Up Value'}>} ReturnFields )

>} Sum_AMT))

Re: Dynamic Set Analysis

richard_pearce6 — Tue, 10 Sep 2013 14:20:45 GMT

The issue I have is the conditional statements would be in the Set Analysis driven by the Meta table example in my first post. So the user will select CalcID, this would filter the Meta table to show the two Columns FieldName and FieldValue.

The expression would need to be dynamic to only perform Set Analysis on the fields in the Meta table.

Something like:

Sum({<

If('FieldA'= [True in Meta], FieldA = { [Corresponding Fields in Meta] } , FieldA = {"*"}

, If('FieldB'= [True in Meta], FieldB = { [Corresponding Fields in Meta] } , FieldB = {"*"}

, If('FieldC'= [True in Meta], FieldC = { [Corresponding Fields in Meta] } , FieldC = {"*"}

>} Sum_AMT))

Rich

Re: Dynamic Set Analysis

Oleg_Troyansky — Tue, 10 Sep 2013 19:50:14 GMT

Richard,

check out this blog article:

Q-Tip #6 - Those Tricky $-Sign Expansions | Natural Synergies

At the end of the article, I explain an example of how to do just that.

best,

Oleg Troyansky