topic Re: Concatenating in For Each loop in QlikView

Concatenating in For Each loop

martyn_birzys — Tue, 21 Apr 2015 08:51:49 GMT

At the first iteration of the loop I already need to have a table in order Concatenate to work.

Therefore I'm creating dummy fields before the loop and then dropping those fields when loop is finished.

Is there a neater way of getting around this?

SLAccounts:

LOAD * INLINE

[temp3, temp4

1, 1

];

for each CoName in 'Company1', 'Company2', 'Company3', 'Company4'

Concatenate

SLAccounts:

LOAD '$(CoName)' as CompanyName,

CustomerAccountNumber,

AccountBalance;

SQL SELECT CustomerAccountNumber,

AccountBalance

FROM "$(CoName)".dbo.SLCustomerAccount;

drop fields temp3, temp4;

Re: Concatenating in For Each loop

Gysbert_Wassenaar — Tue, 21 Apr 2015 09:23:14 GMT

Maybe like this:

SET vConcatenate = ;

For each CoName in 'Company1', 'Company2', 'Company3', 'Company4'

$(vConcatenate)

SLAccounts:

LOAD '$(CoName)' as CompanyName,

CustomerAccountNumber,

AccountBalance;

SQL SELECT CustomerAccountNumber,

AccountBalance

FROM "$(CoName)".dbo.SLCustomerAccount;

SET vConcatenate = Concatenate;

Re: Concatenating in For Each loop

ali_hijazi — Tue, 21 Apr 2015 09:29:17 GMT

if the tables contain the same column names then auto-concatenate will be applied by Qlik

so your script may be something like this:

For each CoName in 'Company1', 'Company2', 'Company3', 'Company4'

SLAccounts:

LOAD '$(CoName)' as CompanyName,

CustomerAccountNumber,

AccountBalance;

SQL SELECT CustomerAccountNumber,

AccountBalance

FROM "$(CoName)".dbo.SLCustomerAccount;

and the final table will be called SLAccounts

Re: Concatenating in For Each loop

martyn_birzys — Tue, 21 Apr 2015 09:30:01 GMT

Tested in Sense, it highlights as error in script, but the script runs ok.

Re: Concatenating in For Each loop

martyn_birzys — Tue, 21 Apr 2015 11:24:20 GMT

The issue I had that columns weren't the same and they did not concatenate correctly. Thanks.

Re: Concatenating in For Each loop

jfkinspari — Thu, 19 Apr 2018 14:06:44 GMT

Hi Martyn

Did you find a way to script the loop without the visual error?