Loop - check whether one record has several inside

[datanibbler]

Hi,

in the context of a larger subroutine, I have a small piece of code to

- parse through the contents of a list one record at a time

- check whether one of the records has several records inside (look at the attached sample and you'll see)

- if so, join a fixed '1' to the table being parsed, using the iteration_nr (which is identical to the row_nr. in the table)

- if not, join a '0' to the table being parsed

=> That binary field (1 or 0) will be used in the next step to split the table in two.

In the attached sample, three records are in order, so they should get a 0 joined to them as per the piece of code I post here;

<=> One record has two inside, so it should get a 1 at this point.

The code of the subroutine (being called once per record, four times in this instance) looks like this (the counter_variable is v_line)

SOFA_pre2

LOAD
     Mat as Mat_pre
  RESIDENT SOFA_Liste_v1
  WHERE Line_SOFA_pre = $(v_line);
  NoConcatenate
  SOFA_post:
  LOAD
      *
  From_Field (SOFA_pre2, Mat_pre)
  (no labels);
  DROP TABLE SOFA_pre2;
  IF NoOfRows('SOFA_post') > 1 THEN
   LEFT JOIN (SOFA_Liste_v1)
   FIRST 1 LOAD $(v_line) as Line_SOFA_pre, 1 as Flag_Multi RESIDENT SOFA_post;
  ELSE
   LEFT JOIN (SOFA_Liste_v1)
   FIRST 1 LOAD $(v_line) as Line_SOFA_pre, 0 as Flag_Multi RESIDENT SOFA_post;
  END IF
  DROP TABLE SOFA_post;

Right now, that does not work - of those four records, only the first one (which is good as per that logic) gets a 0 joined to it. This piece of code runs fine and looking at the Debugger, I see it goes through the lines and it correctly identifies the last record as a "multi_record" and it goes into the IF_THEN_block - otherwise, it always jumps to the ELSE_block - but for some reason, the JOIN doesn't work - and in the next step, only one record is declared "clean" because it has the 0.

Can anybody help me there?`

Thanks a lot!

Best regards,

DataNibbler

[flipside]

I can't quite get my head around what you are trying to do, but think it may be easier to use the iter function. Try this code against your sample excel file. I'm sure you could re-code to suit your 1 and 0 requirement.

LOAD  ROWNO() as rowref, * WHERE LEN(TRIM([Material/Stückzahl]))>0;
LOAD
iterno() as iterref,
subfield([Material/Stückzahl],chr(10),iterno()) as [Material/Stückzahl],
Lieferant
FROM
[MultiRow.xlsx]
(ooxml, embedded labels, table is Sheet1)
while iterno()<=substringcount([Material/Stückzahl],chr(10))+1;

flipside

[datanibbler]

Hi flipside,

I understand. The

>> substringcount([field], chr(10)) <<

tells me whether any of the records has a linebreak.

<=> However, my issue is seemingly not in the identification of "multi_records": That seems to work fine using the FROM_FIELD thing. I will try your variant, too, but that's only the first half of what this loop has to do.

The second half is where I seem to have an issue: A flag_field has to be added to the base_table (SOFA_Liste_v1 in my example): 1 for the "multi_records" (that contain a line_break) and ideally a 0 for the others - so that in the next step I have a binary field that I can use to split my base_table (SOFA_Liste_v1) in two - one table with three "clean" records and one table with one "multi_record"

Can you tell what might be malfunctioning about the JOIN thing?

[flipside]

Maybe stick this after my first code ...

left  join (data)
Load
Lieferent,
RangeMin(max(iterref)-1,1) as flag
resident data group by Lieferant;

flipside

[flipside]

I haven't inspected it greatly but it could be that you are trying to create the same field Flag_multi twice. Once for zeros and again for 1s. Try creating different fields and see if it works then.

[datanibbler]

Hi flipside,

I'm just looking at my code in the debugger and it seems that after the first iteration of this small loop, the base_table SOFA_Liste_v1 is somehow reduced to just one record and thus in the second iteration nothing is loaded because of the WHERE_clause - so the FROM_FIELD must also fail.

Strange ...

[datanibbler]

Hi,

I just realized - and confirmed from the help_file - that the iterno() fct does not avail me here. It only ever gets updated if you have a WHILE clause - but this is a subroutine that is called in a FOR...NEXT loop - so iterno() always returns 1, even while my counter_variable i11 is updated from 1 through 4. Is that correct or have I misunderstood you somewhere?

[datanibbler]

I think I have found a way.

I have recoded that little loop, basing it on the substringcount that you proposed.

That works just as well.

The difference is, now I don't try to perform a JOIN in every iteration, but I "collect" the flagfield, together with a line_nr in a temp_table that finally has 4 records, each with a line_nr. and

- 3 with a 0 as Flag_Multi

- 1 with a 1 as Flag_Multi

=> Following completion of the loop, I join that entire table to my base_list via the line_nr.

=> Finally, my base_table has 4 records like before, 3 with the 0 as flagfield, 1 with the 1

=> I can now split the table nicely.

I will run it once again on my six tables (of which one is empty and the others might be all good or bad in varying degrees) and then I'll wrap it up.

Thanks a lot for your help!

Best regards,

DataNibbler

[flipside]

Looks like you are getting close to a solution, but to clarify I was proposing the iterno function as an alternative to a for..next loop. As ever, there are multiple ways to get a problem solved!

flipside

[datanibbler]

That is very true.

There are many ways to get to Rome.

I got that piece of code to run fine yesterday - the code as a whole even works when one of the six lists I have as a basis is empty. Now I have another issue 😉 I need to remove all interim_qvd_files in the courrse of an iteration of the whole so they cannot be appended to the result the next time round - in case one list was empty and there is no new one.

Let's see...