Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
martyn_birzys
Creator
Creator

Concatenating in For Each loop

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;

1 Solution

Accepted Solutions
ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes

View solution in original post

5 Replies
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


talk is cheap, supply exceeds demand
ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
martyn_birzys
Creator
Creator
Author

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

martyn_birzys
Creator
Creator
Author

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

jfkinspari
Partner - Specialist
Partner - Specialist

Hi Martyn

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