Need help on For ...Next statement

[#1]

Dear Qlik Experts,

Recently I have developed an app that uses a tedious method to join various tables below.

So the Expenses2 table is a resident load from the Expense table with different Fiscal Year by using the WHERE statement. The reason why I do this is because the VALUES field is on a single column. So I want to create multiple columns by different Fiscal year dimension so that I can easily do certain variance calculation. The fiscal year has different dimension values going by 2013, 2014, 2015 , 2016 and Budget.

This is a very time consuming . I was thinking if I am able to re-create this by using a loop such as FOR...Next Loop so that it will loop through and join each table separately.

Expenses2:
NoConcatenate
Load

Period,
     [Cost Center],
     [Cost Element],
     [Cost element name],
     [VALUES] as BudVal,
     [Offsetting acct no.],
     [Name of offsetting account],
     [Document Header Text],
     Name,
     [User Name],
     [Purchase order text],
     [Purchasing Document],
     [Ref Document Number],
     [Aux. acct assignment_1],
     [Document Date],
     [Fiscal Year],
     Vendor,
     [Vendor Name1]

resident Expenses
where [Fiscal Year]='Budget';

Join
Load
Period,
     [Cost Center],
     [Cost Element],
     [Cost element name],
    [VALUES] as [2016 Val],
     [Offsetting acct no.],
     [Name of offsetting account],
     [Document Header Text],
     Name,
     [User Name],
     [Purchase order text],
     [Purchasing Document],
     [Ref Document Number],
     [Aux. acct assignment_1],
     [Document Date],
     [Fiscal Year] ,
     Vendor,
     [Vendor Name1]

resident Expenses
where [Fiscal Year]='2016';

Join
Load
Period,
     [Cost Center],
     [Cost Element],
     [Cost element name],
   [VALUES] as [2015 Val],
     [Offsetting acct no.],
     [Name of offsetting account],
     [Document Header Text],
     Name,
     [User Name],
     [Purchase order text],
     [Purchasing Document],
     [Ref Document Number],
     [Aux. acct assignment_1],
     [Document Date],
     [Fiscal Year] ,
     Vendor,
     [Vendor Name1]

resident Expenses
where [Fiscal Year]='2015';

Join
Load
Period,
     [Cost Center],
     [Cost Element],
     [Cost element name],
    [VALUES] as [2014 Val],
     [Offsetting acct no.],
     [Name of offsetting account],
     [Document Header Text],
     Name,
     [User Name],
     [Purchase order text],
     [Purchasing Document],
     [Ref Document Number],
     [Aux. acct assignment_1],
     [Document Date],
     [Fiscal Year]  ,
     Vendor,
     [Vendor Name1]

resident Expenses
where [Fiscal Year]='2014';

Join
Load
Period,
     [Cost Center],
     [Cost Element],
     [Cost element name],
     [VALUES] as [2013 Val],
     [Offsetting acct no.],
     [Name of offsetting account],
     [Document Header Text],
     Name,
     [User Name],
     [Purchase order text],
     [Purchasing Document],
     [Ref Document Number],
     [Aux. acct assignment_1],
     [Document Date],
     [Fiscal Year] ,
     Vendor,
     [Vendor Name1]

resident Expenses
where [Fiscal Year]='2013';

DROP Table Expenses;

[Anonymous]

Hi,

I would suggest, perform the variance calculation in front end using set analysis.

[zhadrakas · Accepted solution]

you can do it in script too if you want. Look at this script

Expenses_TMP:
LOAD * INLINE [
Fiscal Year, VALUES, TEST, TMP
Budget, 10023, A, F
2016, 1213354, B, G
2015, 234235, C, H
2014, 5645, D, J
];

TMP_Loop:
Load Distinct [Fiscal Year] Resident Expenses_TMP;

//loop through each distinct value of Fiscal Year
For i = 1 to NoOfRows('TMP_Loop');

LET vFiscalYear = Peek('Fiscal Year', $(i)-1, 'TMP_Loop');
LET vField = '$(vFiscalYear)' & '_VAL';

//First time wihout join, then always join
if $(i) > 1 Then
join Load *,
[VALUES] as '$(vField)'
Resident Expenses_TMP
Where [Fiscal Year]='$(vFiscalYear)'
;
else
Expenses:
Load *,
[VALUES] as '$(vField)'
Resident Expenses_TMP
Where [Fiscal Year]='$(vFiscalYear)'
;
end if
next i

//cleanup
drop tables TMP_Loop, Expenses_TMP;
LET vFiscalYear=;
LEt vField=;

EXIT script;

[#4]

Hi Payal,

it wont be good enough. This is because, I am creating a straight table and will like the dimension (i.e. Fiscal year) to be along the column. Currently, the dimension for Fiscal Year is loaded along the rows.

[Gysbert_Wassenaar]

Perhaps like this:

Expenses2:

LOAD 0 as Period Autogenerate 0;

FOR Each vYear in 'Budget','2013','2014','2015','2016'

JOIN(Expenses2)

Load

Period,
     [Cost Center],
     [Cost Element],
     [Cost element name],
     [VALUES] as '$(vYear)',
     [Offsetting acct no.],
     [Name of offsetting account],
     [Document Header Text],
     Name,
     [User Name],
     [Purchase order text],
     [Purchasing Document],
     [Ref Document Number],
     [Aux. acct assignment_1],
     [Document Date],
     [Fiscal Year],
     Vendor,
     [Vendor Name1]

resident Expenses
where [Fiscal Year]='$(vYear)';

Next

[rubenmarin]

Hi Tjoe, having different Fiscal Years the join will never find a row that was loaded in a previous table, so it will add another row as the default Join is an 'Outer Join'.

If I'm not wrong with the previous state, using concatenate instenad of join will give better performance and the same results.

[#7]

Hi Tim,

This works as expected. And it is great as it deals with dynamic situations such as when Fiscal year increases to include 2017 as well. The script :

TMP_Loop:

Load Distinct [Fiscal Year] Resident Expenses_TMP;

This is a very nice trick and coupling it with the Peek function to get the fiscal year into the variable.

First time I am seeing this.

[#8]

Hi Gysbert,

This works as well. But I am going with Tim answer because it deals with dynamic situations in the Fiscal year field.

[mkelemen]

Hi,

here's the loop

Expenses2:

NoConcatenate

Load

...

  ,VALUES as BudVal

Resident

  Expenses

Where

  [Fiscal Year] = 'Budget'

;

For vYear = 2013 to 2016

  Join (Expenses2)

  Load

     ... 

     ,VALUES as [$(vYear) Val]

  Resident

     Expenses

  Where

       [Fiscal Year] = $(vYear)

  ;

Next

BUT

Is this code giving you the result you need?

The way qlik joins work is all fields that have the same name are used as join conditions. So you will join over same document dates, references, header texts, ... This seems strange to me.

BR,

  Matus

[#10]

Hi Ruben,

Thanks for the advice. I will look into it, I am still getting my head round joins and concatenation.

I know I should never do an inner joins because some rows will go missing if there no corresponding row in the first table.