topic Re: this can't be that difficult.. :) in QlikView

this can't be that difficult.. :)

giakoum — Tue, 20 May 2014 11:35:27 GMT

Hi all.

I have a set of sales. Every time some dimension of the sale changes, a new record is inserted with the change, mentioning new and old values. For example :

AA, HR, HRold, Value, ValueOld

1, 100, , 10,

This is a new record so no old values

1, 110, 100, 10, 10

Same record changes HR (from 100 to 110) so old & new values are recorded as well and a new updated record is inserted in the table

Finally the data table looks like this :

1, 100,, 10, (new)

1, 110, 100, 10, 10 (updated HR)

1, 110,, 9, (updated value)

2, 200,, 20, (new)

2, 210, 200, 9, 20 (updated HR & Value)

3, 100, , 5, (new)

4, 220, , 7, (new)

Records with AA 1 and 2 have been updated, that is why they appear multiple times. Records 3 & 4 have not been updated, so they appear only once.

the result I need is for each HR code value that has been added (in) and value that has been removed (out) in one table:

HR In Out

100 15 -10

110 19 0

200 20 -20

210 9 0

220 7 0

Not possible to make calculations in the script as the actual app is much more complicated and the user needs to make selections and comparisons in the front end.

Attached is a sample application.

Re: this can't be that difficult.. :)

tresB — Tue, 20 May 2014 11:54:52 GMT

Could not get the logic of (out).

Re: this can't be that difficult.. :)

giakoum — Tue, 20 May 2014 12:04:10 GMT

For record with AA 1, HR changed from 100 to 110. So it is -10 (Out) for 100, and +10 (In) for 110. Same for AA 2 : HR changed from 200 to 220, so it is -20 (Out) for 200 and +9 (In) for 220 (it is not +20 (in) for 220 because value changed from 20 to 9 at the same time).

Hope this makes sense!

Re: this can't be that difficult.. :)

giakoum — Tue, 20 May 2014 14:24:35 GMT

based on this discussion Evaluating "sets" in the context of a dimension

this solution works but it is unacceptable for the data volume in question :

If(HR=100 and not IsNull(HRold), Sum(TOTAL {$<HRold={"100"}>} MarginOld) * -1,

If(HR=110 and not IsNull(HRold), Sum(TOTAL {$<HRold={"110"}>} MarginOld) * -1,

If(HR=210 and not IsNull(HRold), Sum(TOTAL {$<HRold={"210"}>} MarginOld) * -1,

If(HR=220 and not IsNull(HRold), Sum(TOTAL {$<HRold={"220"}>} MarginOld) * -1,

If(HR=200 and not IsNull(HRold), Sum(TOTAL {$<HRold={"200"}>} MarginOld) * -1,

)

This expression could be created in the script, so no need to hard code it, however its performance is ...bad! Any better idea?

Re: this can't be that difficult.. :)

Tue, 20 May 2014 14:33:57 GMT

Have you tried a pick(match() ,....) solution to see how the performance looks like??

Re: this can't be that difficult.. :)

Anonymous — Tue, 20 May 2014 20:36:29 GMT

For In you could try Aggr(sum(Value), HR)

For out you could try Aggr(minstring(HR)-maxstring(HR), AA)

I'm less certain about the second one so someone else msy be able provide a better idea.

Re: this can't be that difficult.. :)

giakoum — Wed, 21 May 2014 07:08:48 GMT

Hi Nikos

still bad, as I have 4000 distinct values in HR and 30m records overall.

Thank you

Re: this can't be that difficult.. :)

giakoum — Wed, 21 May 2014 07:09:06 GMT

Thank you Bella, but this will not work in this case