Concatenating in For Each loop

[martyn_birzys]

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;

Next

drop fields temp3, temp4;

[Gysbert_Wassenaar]

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;

Next

[ali_hijazi · Accepted solution]

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;

Next

and the final table will be called SLAccounts

[martyn_birzys]

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

[martyn_birzys]

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

[jfkinspari]

Hi Martyn

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