topic Re: Do While Loop in QlikView

Do While Loop

Anonymous — Mon, 09 Nov 2015 10:30:56 GMT

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 — Mon, 09 Nov 2015 12:37:41 GMT

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

Anonymous — Mon, 09 Nov 2015 12:38:26 GMT

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

Re: Do While Loop

swuehl — Mon, 09 Nov 2015 12:41:36 GMT

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

flipside — Mon, 09 Nov 2015 12:55:02 GMT

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 — Mon, 09 Nov 2015 13:01:24 GMT

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;

Re: Do While Loop

Anonymous — Tue, 10 Nov 2015 09:45:11 GMT

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

Re: Do While Loop

flipside — Tue, 10 Nov 2015 10:40:20 GMT

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