Do While Loop

[Anonymous]

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

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

[Anonymous]

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

[swuehl]

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.

[flipside]

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.

[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;

[Anonymous]

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

[flipside]

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