topic headscratcher: script works with one set of data, not another in QlikView

headscratcher: script works with one set of data, not another

Wed, 11 Oct 2017 14:10:41 GMT

Hello people

I've been trying to put together a script to calculate the total sum of business days covered by a report.

The point is letting users define the starting and ending month on load and calculate accurate occupation rates.

So I'm defining variables for user input

LET vReport.Start = Input('jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec', 'FIRST MONTH OF YOUR REPORT?');

LET vReport.NbMonths = Input('How many months does your report cover', 'ALMOST DONE');

Then load a set of data defining the number of business days for each month of the year

LOAD lMonths, Bizdays_permonth

inline [

lMonths, Bizdays_permonth

jan, 22

feb, 20

mar, 22

apr, 20

may, 19

jun, 21

jul, 22

aug, 22

sep, 21

oct, 22

nov, 21

dec, 20

];

Then calculate / identify the rows for starting month, and ending month

LET vReport.StartNb = FieldIndex('lMonths',vReport.Start);

LET vReport.End = FieldValue('lMonths',vReport.StartNb + vReport.NbMonths - 1);

LET vReport.EndNb = FieldIndex('lMonths',vReport.End);

Then execute a simple loop to accumulate the values from starting to ending month

LET vCounter = vReport.StartNb;

LET vSum = FieldValue('Bizdays_permonth',vCounter);

DO WHILE vCounter < vReport.EndNb ;

LET vCounter = vCounter + 1;

LET vTemp = FieldValue('Bizdays_permonth',vCounter);

LET vSum = vSum + vTemp;

LOOP;

The mystery is as follows: calculation with dataset above is always wrong.

Say I start in February, for 3 months. Result should be 20 + 22 + 20 = 62

But script outcomes 60.

However exact same script, with different values below actually comes to correct result...

Feb, for 3 months, outcomes 2 + 3 + 4 = 9

LOAD lMonths, Bizdays_permonth

inline [

lMonths, Bizdays_permonth

jan, 1

feb, 2

mar, 3

apr, 4

may, 5

jun, 6

jul, 7

aug, 8

sep, 9

oct, 10

nov, 11

dec, 12

];

I'm sure it must be a total fluke of mathematical magic, but. Any idea?

Attaching test file so you can see it work.

I've tried step by step debugging, the problem seems to occur in the loop.

Thanks for your help!

Re: headscratcher: script works with one set of data, not another

Peter_Cammaert — Wed, 11 Oct 2017 22:02:59 GMT

Well, the explanation for this behavior is rather simple. Function FieldValue() returns the value that is present in the position as specified by the second parameter. But those values are lifted from the Symbol table of the field, not from the table column(s) that make up a particular field. And a field Symbol table stores every distinct value only once.

For the working data, the symbol table of field Bizdays_permonth contains these values:

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

For the non-working data, the symbol table of field Bizdays_permonth contains these values:

22, 20, 19, 21

and nothing more...

The order in which symbol table strings are stored is the one in which you load them. So position 3 contains the value from row 5 (may) and position 4 contains the value from row 6 (jun).

If you want to fix this using the code you already wrote, replace the FieldValue() function by a call to Lookup(). That one does peek into the column of the internal table, instead of the symbol table.

Best,

Peter

Re: headscratcher: script works with one set of data, not another

swuehl — Wed, 11 Oct 2017 22:42:20 GMT

Peter is absolutely right, and that's why you shouldn't use FieldValue() to query data table values.

You can probably use Lookup(), but I think the straight forward replacement would be the Peek() function.

[Just take care about the different index start compared to FieldIndex(), Peek() starts table record indexing from 0]

LET vSum = Peek('Bizdays_permonth',vCounter-1);

DO WHILE vCounter < vReport.EndNb ;

LET vCounter = vCounter + 1;

LET vTemp = Peek('Bizdays_permonth',vCounter-1);

Besides this, I wouldn't do the aggregation of your table values like this, using a DO WHILE loop and Peek() the table record values, but that's not the topic of your thread nor your issue.

Re: headscratcher: script works with one set of data, not another

Thu, 12 Oct 2017 07:59:48 GMT

Aww, total noob mistake

That's great, thank you guys!

I must admit, I've only started recently, and I like to tinker - if the script had not worked at all I would have kept looking. But I was just confused by the inconsistency.

Well, thanks again for taking the time