topic Re: calculating value from different rows in QlikView

calculating value from different rows

Fri, 21 Feb 2014 13:58:08 GMT

Hello community,

sorry, the title might not be "very" clear.

This is about calculating the net value of a gross value based on the value of two other fields.

My Task:

I have debtors who have an order number with one or more position(s) inside each order. Each position has a condition type (tax or discount) with an according numeric value in another column.

Here's an example:

Debitor	OrderNo	Position	ConditionType	ConditionValue
Debtor A	1	010	TAX	500
Debtor A	1	020	DISC	20

If the value in ConditionType TAX is > 0 and DISC exists and is > 0 then I want to calculate the net DISC value (in this case divide 20/1,19).

I have no clue how to do this since these information are stored in different rows.

Hope you have an idea.

thank you,

Thorsten

Re: calculating value from different rows

Fri, 21 Feb 2014 15:05:57 GMT

Thorsten,

1) I would load a temp table where you will populate the valid OrderNo

(several tables if you have several conditions on different lines)

Temp1:

LOAD OrderNo as TemporderNo1

WHERE ConditionType= 'TAX' and ConditionalValue > 0

// if there are several conditions, each time with an AND logic

Temp2:

LOAD OrderNo as TemporderNo2

WHERE ConditionType= 'DISC' and ConditionalValue > 0 and exists('TemporderNo1' OrderNo)

2) I would load the final table based on the existence of the temp field populated with the tests

FinalTable:

LOAD ....

ConditionalValue/1,19 as netvalue

FROM ...

Where exists('Temporderno2', OrderNo) and ConditionType= 'DISC'

Fabrice

Re: calculating value from different rows

Fri, 21 Feb 2014 17:21:57 GMT

Hey Thorsten,

Please check out the attached file and see if it helps. I used the expression as Value/119. You can change it per your needs. This will give you an idea of how to proceed.

Hope it helps

Thanks

Re: calculating value from different rows

maxgro — Fri, 21 Feb 2014 17:22:06 GMT

If you have maximum two rows for Debitor, OrderNo you can join the two rows to have 1 row with all fields for every Debitor, OrderNo

Calculation will be simplified

Re: calculating value from different rows

rwunderlich — Fri, 21 Feb 2014 17:52:25 GMT

Assuming Debtior and OrderNo are your chart dimensions, you can something like this:

=if(sum({<ConditionType={'TAX'}>}ConditionValue)>0

,sum({<ConditionType={'DISC'}>}ConditionValue) / 1.19

,sum({<ConditionType={'DISC'}>}ConditionValue)

)

See attached.

Re: calculating value from different rows

Mon, 24 Feb 2014 06:29:23 GMT

thanks a lot for the examples. I will go through them and report back to you.

UPDATE:

I've tested several approaches and I think Rob's solution is the most suitable for me right now.

Thanks a lot again.