Calculation problem

[datanibbler]

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

[Gysbert_Wassenaar]

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

[datanibbler]

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?

[Gysbert_Wassenaar]

Perhaps something like in the attached qvw.

[datanibbler]

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

[Gysbert_Wassenaar]

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.

[datanibbler]

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