10 Replies Latest reply: Oct 17, 2014 3:31 AM by Friedrich Hofmann RSS

    Loop - check whether one record has several inside

    Friedrich Hofmann


      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

       

        • Re: Loop - check whether one record has several inside
          Dave Riley

          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

            • Re: Loop - check whether one record has several inside
              Friedrich Hofmann

              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?

              • Re: Loop - check whether one record has several inside
                Dave Riley

                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

                  • Re: Loop - check whether one record has several inside
                    Friedrich Hofmann

                    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 ...

                      • Re: Loop - check whether one record has several inside
                        Friedrich Hofmann

                        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?

                          • Re: Loop - check whether one record has several inside
                            Friedrich Hofmann

                            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

                            • Re: Loop - check whether one record has several inside
                              Dave Riley

                              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

                                • Re: Loop - check whether one record has several inside
                                  Friedrich Hofmann

                                  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...

                                    • Re: Loop - check whether one record has several inside
                                      Friedrich Hofmann

                                      Hi flipside,

                                       

                                      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...