topic Re: Building a FOR...NEXT loop based on Row_nrs. in QlikView

Building a FOR...NEXT loop based on Row_nrs.

datanibbler — Thu, 07 Nov 2013 10:43:10 GMT

Hi,

I have a preliminary question:

- I want to build a LOOP over all the lines of a table I have just loaded.

- In the LOAD statement, I load >>> RowNo() as Line <<<.

- It seems, however that I cannot define variables for the lower and upper bounds (based on that field) in the script.

I tried several times now - I can define those variables on the GUI no problem, but not in the script.

<=> I need those bounds in some form (variables or Fields) so I can set up my LOOP.

Can someone help me there, please?

Thanks a lot!

Best regards,

DataNibbler

Re: Building a FOR...NEXT loop based on Row_nrs.

Gysbert_Wassenaar — Thu, 07 Nov 2013 10:52:49 GMT

for i = 1 to noofrows('TableName')

// do stuff

Re: Building a FOR...NEXT loop based on Row_nrs.

jonathandienst — Thu, 07 Nov 2013 10:53:09 GMT

The lower bound will be 0 (for peek) or 1 for row number. The function NoOfRows('TableName') will in script return the number of rows loaded, so the upper bound would by that value (or value - 1 for peek).

Let zSize = NoOfRows('TableName');

For zi = 0 To zSize - 1

Let zValue = Peek('MyField', zi, 'TableName');

... do something here ...

Set zi = ;

Set zSize = ;

Set zValue = ;

(Or have I misunderstood you?)

HTH

Jonathan

Re: Building a FOR...NEXT loop based on Row_nrs.

datanibbler — Thu, 07 Nov 2013 11:35:37 GMT

Hi Jonathan,

NoOfRows() is a function I have used in another scenario already. I can well use that.

I merely think you have misunderstood me insofar as your code seems to be for something more complicated than I want - in the end, it is going to be quite complicated, but not at this stage. I will explain the entire scope of this task so you can better understand just what I need:

- I have, with some help from swuehl, developed a code that works fine for ONE employee. It involves

- a MAPPING LOAD

- several LOAD statements (from different tables, some of them concatenated)

- a "blowup" of a table (to one_record_per_day)

- The next step is to execute that code in a LOOP for every employee we have

=> I have already developed a LOAD from the masterdata table that gives me 1 record per employee

=> Now I want to build the LOOP over that table

<=> However, that LOOP would be very large and probably quite hard to oversee. I wonder whether it might not be a better idea to

- develop a subroutine (well, that would just be the code I already have working on ONE employee_nr)

- build a LOOP over all the lines of that masterdata_table and in that LOOP

- define a variable with the emp_id I find in that line

- call the subroutine and pass that id_variable as a parameter to the subroutine.

I have very limited experience with subroutines, but I think there's nothing much to it, it's basically just a script.

Do you know of any pitfalls I have to keep in mind (like any customary script_commands that will NOT work inside a subroutine)?

Thanks a lot!

Best regards,

DataNibbler

I think that's better.

Is there

Re: Building a FOR...NEXT loop based on Row_nrs.

datanibbler — Thu, 07 Nov 2013 11:55:58 GMT

Hi,

I thought I was now underway - I would first develop the code to - still for ONE employee - take the data RESIDENT from my base_load and via SQL from the second database_table - but now I am stopped by a thing that should actually be very simple - the way big projects often fail because of very little things...

=> I still cannot seem to define (or rather, I cannot check that I have successfully defined) a variable in the script:

- I have a RESIDENT LOAD now that loads just one record, for one employee

     - In the second table, I cannot count on the row_nrs being the same, so I have to filter that second LOAD using
       the emp_id.
       => To that end I have to somehow put the emp_id (which is in a field in that one loaded record) in a variable.

<=> Using the usual syntax with LET, I get no error, but I cannot see that variable on GUI-level - and I don't trust in its existence if I cannot see it...

Can you tell me what I am doing wrong there?

Thanks a lot!

Best regards,

DataNibbler

P.S.: OK, I have it - it's awesome how reading up in the help_file often helps - sometimes combined with some guessing at what could be the right keyword to look for - in this case, the PEEK() function helped: In this scenario, I just loaded only one record, so I don't have to specify anything else, just the field_name.