topic Re: Need help on For ...Next statement in QlikView

Need help on For ...Next statement

Thu, 22 Dec 2016 08:17:32 GMT

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;

Re: Need help on For ...Next statement

Anonymous — Thu, 22 Dec 2016 08:32:38 GMT

Hi,

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

Re: Need help on For ...Next statement

zhadrakas — Thu, 22 Dec 2016 08:37:03 GMT

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;

Re: Need help on For ...Next statement

Thu, 22 Dec 2016 08:37:28 GMT

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.

Re: Need help on For ...Next statement

Gysbert_Wassenaar — Thu, 22 Dec 2016 08:41:18 GMT

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)';

Re: Need help on For ...Next statement

rubenmarin — Thu, 22 Dec 2016 08:57:25 GMT

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.

Re: Need help on For ...Next statement

Thu, 22 Dec 2016 08:57:39 GMT

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.

Re: Need help on For ...Next statement

Thu, 22 Dec 2016 08:58:39 GMT

Hi Gysbert,

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

Re: Need help on For ...Next statement

mkelemen — Thu, 22 Dec 2016 09:01:36 GMT

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)

;

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

Re: Need help on For ...Next statement

Thu, 22 Dec 2016 09:01:40 GMT

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.

Re: Need help on For ...Next statement

rubenmarin — Thu, 22 Dec 2016 09:02:55 GMT

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)'

Re: Need help on For ...Next statement

Thu, 22 Dec 2016 09:04:24 GMT

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?

Re: Need help on For ...Next statement

Thu, 22 Dec 2016 09:06:02 GMT

ok thanks I will try this out as well.

Re: Need help on For ...Next statement

rubenmarin — Thu, 22 Dec 2016 09:10:26 GMT

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

LOAD * Inline [

];

join LOAD * Inline [

];

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

Re: Need help on For ...Next statement

mkelemen — Thu, 22 Dec 2016 09:11:47 GMT

So why not use concatenate instead of join then?

Re: Need help on For ...Next statement

mkelemen — Thu, 22 Dec 2016 09:15:28 GMT

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.

Re: Need help on For ...Next statement

Thu, 22 Dec 2016 09:16:08 GMT

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.

Re: Need help on For ...Next statement

Thu, 22 Dec 2016 09:17:23 GMT

Yes Matus you are right as well.

I should perform a concatenate rather than do a join.