topic Re: How to Refer to Expression in ValueLoop in QlikView

How to Refer to Expression in ValueLoop

Anonymous — Tue, 04 Apr 2017 11:14:50 GMT

Dear All,

Let say I have a simple ValueLoop as below as my Dimension:

ValueLoop(1,3)

Then in my expression, It will be as below:

Pick(ValueLoop(1,3),

Sum(DebitAmount),

Sum(CreditAmount),

Sum(DebitAmont)-Sum(CreditAmount)

)

May I know instead of putting the calculation in the 3rd pick valueloop, is there anyway I can do the calculation by referring the result of pick 1 and pick 2 as below:

Pick(ValueLoop(1,3),

Sum(DebitAmount),

Sum(CreditAmount),

ValueLoop1 - ValueLoop2 (What script should be used here?)

)

Hope to get advice from experts here.

Thanks in advance!

Re: How to Refer to Expression in ValueLoop

vinieme12 — Tue, 04 Apr 2017 11:24:21 GMT

i don't think it works with valueloop

I suggest you load a dummy table like below

DummyDIM

Rowno() as DIM

Autogenerate(3);

and then change expression to below

Pick(DIM,

Sum(DebitAmount),

Sum(CreditAmount),

Sum(DebitAmount) - Sum(CreditAmount)

)

Also to answer your question (What script should be used here?)

You cannot directly refer something that is evaluated in the same expression

Re: How to Refer to Expression in ValueLoop

Kushal_Chawda — Tue, 04 Apr 2017 11:53:38 GMT

You need to write expression for 3rd pick value loop Sum(DebitAmont)-Sum(CreditAmount) because you are individually evaluating expression for each value.

You would have been able to do it in expression,

for eg you have two expression like

1) Sum(DebitAmount)

2) Sum(CreditAmount)

Now you can create 3rd expression as, column(1)-column(2)

Re: How to Refer to Expression in ValueLoop

Anonymous — Wed, 05 Apr 2017 01:44:55 GMT

Hi Vineeth,

Thank you for you reply. Let me try to look into the possibility of loading the dummy table.

On top of that, is there any method you can introduce? because the development I involved in has a lot similar scenario, and my worry is there will be too much dummy table and caused the future maintenance of the qvw will be very complicated.

Re: How to Refer to Expression in ValueLoop

Anonymous — Wed, 05 Apr 2017 01:49:23 GMT

Hi Kushal,

Thank you for the reply. Do you mean that for my 3rd ValueLoop I must write Sum(DebitAmont)-Sum(CreditAmount) ?

Actually my concern is the 3rd expression or even the subsequent expression will be even longer. The expressions are just samples. But in fact the real expression I'm currently using is way much longer. If I really have no other choice but to write Sum(DebitAmont)-Sum(CreditAmount) as my 3rd ValueLoop, let say I discover the 1st valueloop expression is wrong, means I need to modify by 1st and 3rd valueloop, this will possibly caused tougher future maintenance.

Hope there are other solution that you can suggest?

Thanks again for your effort in replying.

Re: How to Refer to Expression in ValueLoop

vinieme12 — Wed, 05 Apr 2017 06:26:58 GMT

I don't know what you are trying to create, if you can share a snapshot or sample of what you are trying to achieve it'll be easier to assist.

Re: How to Refer to Expression in ValueLoop

Kushal_Chawda — Wed, 05 Apr 2017 06:55:40 GMT

Thank you for the reply. Do you mean that for my 3rd ValueLoop I must write Sum(DebitAmont)-Sum(CreditAmount) ?

Yes, You need to write the entire expression. For the maintenance point of view, you can create variable instead on front end

vExpression1 :Sum(Debit)

vExpression2: Sum(Credit)

Note : Do not use "=" in variable expression

Now in chart you can use below expression

Pick(ValueLoop(1,3),

$(vExpression1) ,

$(vExpression2),

$(vExpression1) - $(vExpression2)

)

Using this approach, you just need to change the expression of vExpression1 or vExpression2 , so that vExpression3 will automatically be using the same formula that you have changed.

Hope this solve your problem