topic Re: Calculation problem in QlikView

Calculation problem

datanibbler — Mon, 24 Nov 2014 10:16:50 GMT

Hi,

I have an Excel list where, in one field, I have a list of values, separated by commas - the number of values there can differ greatly, one day it is 12, the next time (still on the same day) it is 110 and so on.

The difficulty is: In a subroutine, I turn that into as many records as I have values in that field - 12 records out of 1 to stick with the example and 110 out of the next one.

=> I cannot simply sum this up, I'd get an enormous number.

Using >> Sum(DISTINCT) << would solve that problem.
<=> If by chance I got two records with the same number of values in that field, that would be wrong again.

What can I do to avoid both those pitfalls?

I have a unique line_ID - can I use a set_expression or sth. so that the number belonging to one unique line_ID is counted only once?

Thanks a lot!

Best regards,

DataNibbler

Re: Calculation problem

Gysbert_Wassenaar — Mon, 24 Nov 2014 10:26:37 GMT

I don't quite understand what you're trying to do. Can you give a concrete example with source data and the expected results?

Re: Re: Calculation problem

datanibbler — Mon, 24 Nov 2014 10:50:05 GMT

Hi Gysbert,

sure. I attach a small sample file where you see exactly what my problem is:

- On one day, there were 4 checks, with 3 different numbers of items checked.

=> In my subroutine, I make each of those 4 records into as many records as I have items.

=> Obviously, I cannot sum them up. A SUM(DISTINCT) would work if I had only records 1 to 3 - but as it is, it won't work - it would ignore record #4 as it has the same nr. of items as record #1.

I could calculate it in the script.

<=> Is there an easier way?

Re: Re: Re: Calculation problem

Gysbert_Wassenaar — Mon, 24 Nov 2014 10:56:25 GMT

Perhaps something like in the attached qvw.

Re: Re: Calculation problem

datanibbler — Mon, 24 Nov 2014 11:05:12 GMT

Hi Gysbert,

yes. I was thinking of something like this - but if I hard-code a line_ID in the set_expression, I would need approx. 1000 expressions ...

Isn't there a way to tell QlikView - on the GUI - to not add up anymore numbers if the line_ID is the same as above? Alternatively, I could do it in the script - I could populate every record with the same date with the total nr. of items checked - all 4 checks included - and then just use ONLY() or avg() on the GUI to display that.

Best regards,

DataNibbler

Re: Calculation problem

Gysbert_Wassenaar — Mon, 24 Nov 2014 11:18:37 GMT

yes. I was thinking of something like this - but if I hard-code a line_ID in the set_expression, I would need approx. 1000 expressions ...

Why? Each Check in my example has exactly one ItemID with value 1. That's all that's necessary to be able to sum the values.

Re: Re: Calculation problem

datanibbler — Mon, 24 Nov 2014 12:17:52 GMT

Ah. Now I see what you are doing. I've looked at the script.

Only, I've done it in a slightly different way - I have a subroutine which calculates the number_of_items_checked, so I cannot create a WHILE_loop for this, so I cannot use Iterno(). Would be a very good idea otherwise.

I guess I'll try to ´calculate this in the script - I think I know how to do that.

Thanks a lot anyway!

Best regards,

DataNibbler