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;
iterno() as iterref,
subfield([Material/Stückzahl],chr(10),iterno()) as [Material/Stückzahl],
(ooxml, embedded labels, table is Sheet1)
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?
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.
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?
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!
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.
I have another issue now:
=> At one point in the code (just where I entered that check yesterday) I have a check whether the table I loaded is empty => if it is, I actually don't need to process it further and I can leave, so I have an IF_THEN_block and an EXIT SUB within.
<=> The issue is, when I do that, the Subroutine is ended and no qvd_file is saved - well, there's not much of a point if there is nothing, but I have to build something to delete qvd_files - if there are any - after the entire code has run so I won't have issues with old qvd_files the next time round - but in case no qvd_file is created, I cannot delete it...