7 Replies Latest reply: Nov 10, 2015 5:45 AM by Dave Riley RSS

    Do While Loop

    Matthew Davies

      Hi all,

       

      Getting an error when trying to loop through my field names.

       

      Set n=1;

       

       

      Do while ($(n)<9)

       

       

        for each f in 'Territory_From$(n)_Comm1'

        for each t in 'Territory_To$(n)_Comm1'

        for each s in 'Sea_Sum$(n)_Comm1'

       

        Journeys_a:

       

       

        Load

       

       

        UniqueID,

        UniqueID&'.'&$(n) as LineID,

        pick($(n),$(f)) as From,

        pick($(n),$(t)) as To,

        pick($(n),$(s)) as SeaSum

       

       

        resident Quotes;

       

        Let n=$(n)+1;

       

       

      Loop

       

      little piece of background, I create my nth number.

       

      I load in a table using the nth number, then i want to add 1 and loop until it reaches 8 on this occasion. There maybe other ways to do this but I'm looking for this method as I'm going to expand it to replace a few more field names using the looping functions but I've fallen at the first hurdle.

       

      Any help would be great.

       

      Thanks, Matt

        • Re: Do While Loop
          Peter Cammaert

          On average, it helps those 100000+ Community members quite a lot if you can tell us what error your are getting. Reprint the message text or post a screenshot.

           

          BTW You have 4 nested loop constructs (a DO WHILE and 3 FOR statements), but I see only the LOOP terminator and no NEXT at all.

            • Re: Do While Loop
              Matthew Davies

              Thank you, didn't make it clear but the error message i am getting says "Loop".

                • Re: Do While Loop
                  Dave Riley

                  That'll be, as Peter says, the For Each statements each need a Next statement. The error is saying 'Loop' isn't the right syntax to end the "For each s" statement.

                   

                   

                   

                  Something like ...

                   

                  Set n=1;

                   

                   

                  Do while n<9

                   

                   

                    for each f in 'Territory_From$(n)_Comm1'

                    for each t in 'Territory_To$(n)_Comm1'

                    for each s in 'Sea_Sum$(n)_Comm1'

                   

                    Journeys_a:

                   

                   

                    Load

                   

                   

                    UniqueID,

                    UniqueID&'.'&$(n) as LineID,

                    pick($(n),$(f)) as From,

                    pick($(n),$(t)) as To,

                    pick($(n),$(s)) as SeaSum

                   

                   

                    resident Quotes;

                   

                  Next s;

                  Next t;

                  Next f;

                   

                   

                    Let n=$(n)+1;

                   

                   

                  Loop;

                   

                   

                  flipside

              • Re: Do While Loop
                Rudolf Linder

                did you try to define LET n=1 as first Definition (instead of SET n=1)

                • Re: Do While Loop
                  Stefan Wühl

                  Where are you getting the error?

                   

                  And what do you expect to get using the for each ... in loops?

                   

                  You are missing NEXT statements for each FOR statement.

                   

                  I don't understand your logic with the pick(), since each variable f,t,s only holds a single value a time and won't expand to a csv list, as pick() function expects.

                  • Re: Do While Loop
                    Dave Riley

                    In addition to other posts, you also want to set up your loop with Do while n<9 and not Do while ($(n)<9) or Do while $(n)<9.

                     

                    Usually expanding the variable using the $ doesn't matter but it does in this statement.

                     

                     

                    flipside

                     

                     

                    PS You also need to terminate the Loop with a semi-colon, too.

                    • Re: Do While Loop
                      Gysbert Wassenaar

                      Perhaps something like this works better:

                       

                      Temp:

                      CrossTable(FieldName,Value)

                      LOAD *

                      Resident Quotes;

                       

                      Result:

                      LOAD

                           UniqueID,

                           UniqueID & '.' & Mid(FieldName, Index(FieldName,'_')-1,1) as LineID,

                           If(WildMatch(FieldName, 'Territory_From*'),Value) as From,

                           If(WildMatch(FieldName, 'Territory_To*)',Value) as To

                           If(WildMatch(FieldName, 'Sea_Sum*)',Value) as SeaSum,

                      Resident Temp;