"for each x in y" using a variable

[tobias_klett]

Hi,

I am using a variable y as list for the loop statement.

This works fine as long as the variable "y" contains a comma seperated list with two or more values. But if there is only one value than the loop starts without assigning this value to the loop-variable x. I debbuged it an the loop-variable x is NULL.

Thanks for any advice

Tobias

[whiteline]

as a workaround I think you can use:

x=if(SubFieldCount(y, ',')>0, x, y)

[chematos]

How are your getting that values?

I' ve used sometimes the function pick()

pick(n, expr1[ , expr2,...exprN])

Returns the n:th expression in the list. n is an integer between 1 and N.

hope this helps

[tobias_klett]

Depending on a model I have different data sources which I want to loop over.

Within the loop I use the variable aSOURCE for further programming. But in the standard case "STD" is is not "STD" but NULL.

IF $(vsMODEL) ='SD' THEN

   SET vsSOURCE_LIST   = 'DAR','GHK','STU';

ELSE

   SET vsSOURCE_LIST   = 'STD';

ENDIF

for each aSOURCE in $(vsSOURCE_LIST)

[hic]

First, instead of storing your loop variable in a list within a variable, you could store it in a table. Here's an example of how to use a table as input for a for-next loop:

LoopVariables:

Load * inline

[LoopVariable, LoopAttribute

A,3

B,5

C,17];

For vLoopNumber = 1 to NoOfRows('LoopVariables')

  Let vLoopVariable = peek('LoopVariable',vLoopNumber-1,'LoopVariables');

  Let vLoopAttribute = peek('LoopAttribute',vLoopNumber-1,'LoopVariables');

      <Some other statements>

Next vLoopNumber

Secondly, I cannot reproduce your problem when you have only one value in the list. Can you post an example?

HIC

[#6]

Hi Henric,

First, I'm sorry Tobias.Klett to interrupt your topic!

So here I have a small problem,

I have an accounting under the Microsoft Dynamics Navision ERP, through what I have to show the amounts (credit, debit).

The concern here is that I have to take into account the "totaling" which contains the range of Generals accounts.

==> General account number belongs to [Totalisation]

I loaded the necessary data in Qlik View and after I subtracted the terminals of the field "Totalisation" as follows:

Faits1:
LOAD "No_" as [Num_compteG],
Totaling as [Totaling1],
  IF([Totaling]<>'',subfield([Totaling],'..' ,1),'') as [BorneDébut],
IF([Totaling]<>'',subfield([Totaling],'..' ,2),'') as [BorneFin];

SQL SELECT "No_",
Totaling
FROM "CF_final".dbo."COMAF$G_L Account";

and then I concatenated with the table of the accounting records to allow loading amounts debits, credits, also I use a “ for loop” into it as follows:

FOR [Num_compteG]=BorneDébut to BorneFin
 LOAD
 sum("Credit Amount") as [TotalisationCredit];
 NEXT[Num_compteG];
 

But I failed!!

You find the attached origin table on Microsoft Navision "Plan_comptable_NAV.jpg" and the file "Totaling" that I created on Qlik View unfinalized.

If you have an idea to help me thank you!

Have a good day!

[tobias_klett · Accepted solution]

Thanks Hendric for your idea.

I created a sample file and solved it myself doing that...

My first SET Statement:

SET vsSOURCE_LIST   = 'DAR','GHK','STU';

resulted in the following content in the variable

'DAR','GHK','STU'

My second SET Statement with only one Value:

SET vsSOURCE_LIST   = 'STD';

resulted in the follwing content in the variable

STD instead of 'STD'

So I used this LET Statement instead:

LET vsSOURCE_LIST   = chr(39) & 'STD' & chr(39);

[hic]

Hayfa: I am not sure I understand what you are trying to do, but here are my comments:First of all, there are several values of [BorneDébut] and [BorneFin]. Which ones do you want to use?

Secondly, in your for-next loop, you try to access them, but this is not possible since they are not variables - they are fields.

Does [BorneDébut] and [BorneFin] define ranges and you want to calculate the total for each range? If so, you should look at "intervalmatch".

HIC

[#9]

Exactly Henric

[BorneDébut] and [BorneFin] are the first and the second limit (bounds) of the interval, and I want to calculate the Amount into those limits.

But, I don't define them in a var!!

I will try with this, and the "intervalmatch".

A lot of thanks!

Hayfa.