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;

[rubenmarin]

Adapted to Tim's script it can be:

//First time wihout concatenate, then always concatenate
if $(i) > 1 Then
Concatenate (Expenses)

Load *,
[VALUES] as '$(vField)'
Resident Expenses_TMP
Where [Fiscal Year]='$(vFiscalYear)'
;
else
Expenses:

NoConcatenate
Load *,
[VALUES] as '$(vField)'
Resident Expenses_TMP
Where [Fiscal Year]='$(vFiscalYear)'

[#3]

Hi Matus,

Thanks for replying as well. I do not think I will join over same document dates, refrences and header texts, this is because the document dates are all unquie for each different line.

So in this situation it will just act like a concatenation right?

[#4]

ok thanks I will try this out as well.

[rubenmarin]

Hi Matus, that's because the default Join option in Qlik script is 'Outer', you can test with a simple:

LOAD * Inline [

a

1

];

join LOAD * Inline [

a

2

];

'a' will store '1' and '2'. With inner join 'a' doesn't stores any value.

[mkelemen]

So why not use concatenate instead of join then?

[mkelemen]

You're right, thanks. I realized that after I posted so I edited the post.

But I still do not understand why join and not concatenate with different [$year Val] fields.

[#8]

Yes Ruben,

You are right. I should do a concatenation rather than join for better performance because the join in my script is behaving like a concatenation  since the fiscal year and document date field with always be unique for every single line.

[#9]

Yes Matus you are right as well.

I should perform a concatenate rather than do a join.