Same Load Statement - 3 different Dbs

[JustinDallas]

Hello Everyone,

We have an MS Dynamics system with 3 companies in it.  Because of this, we have 3 different databases on the same server with the same structure.  Is there a way for me to store my load text into a variable and then iterate through db connections and load the data intelligently?  I'm trying to avoid having to do something like this.

...
LIB CONNECT TO 'Company 1 (qlik_db_service)'
DATA:
LOAD WidgetName, WidgetId;
SQL SELECT WidgetName, WidgetId
  FROM [dbo].[WID3000];
STORE DATA into [lib://Qlik Data (qlik_db_service)/COMPANY1\WID\Widgets.qvd] (qvd);
DROP Table DATA;

LIB CONNECT TO 'Company 2 (qlik_db_service)'
DATA:
LOAD WidgetName, WidgetId;
SQL SELECT WidgetName, WidgetId
  FROM [dbo].[WID3000];
STORE DATA into [lib://Qlik Data (qlik_db_service)/COMPANY2\WID\Widgets.qvd] (qvd);
DROP Table DATA;
...

One idea I've had is to just copy my Extractor/Loader QVD and then set my db connection and variables in the beginning.  But that's a very brittle solution because if you have to change one loader, you have to make sure that change is replicated amongst all three Loader apps.

Any help is greatly appreciated.

[dwforest · Accepted solution]

Not tested, but I think you could use a sub...

You can make a  SUB

SUB LoadCompany(Company)

     LOAD WidgetName, WidgetId; 

      SQL SELECT WidgetName, WidgetId 

     FROM [dbo].[WID3000]; 

STORE DATA into [lib://Qlik Data (qlik_db_service)/$(Company)\WID\Widgets.qvd] (qvd); 

DROP Table DATA; 

END SUB

Then call

LIB CONNECT TO 'Company 1 (qlik_db_service)' 

LoadCompany('Company1');

LIB CONNECT TO 'Company 2 (qlik_db_service)' 

LoadCompany('Company2');

LIB CONNECT TO 'Company 3 (qlik_db_service)' 

LoadCompany('Company3');

[Nicole-Smith]

A for each loop may work:

FOR EACH i IN 'Company1','Company2','Company3'

Data:
LOAD Value1, 
     Value2
FROM example_data.xlsx (ooxml, embedded labels, table is $(i));

STORE * FROM Data INTO $(i).qvd (qvd);

DROP TABLE Data;

NEXT i

I've attached an example file that this code works with.

You could tweak your code so that the loop replaces the parts that are different for the different companies.

[JustinDallas]

This was my final answer for anyone who is running upon this:

SUB GLAccountCategoryMaster(Company)
DATA:
LOAD [ACCATNUM]
      ,[ACCATDSC]
      ,[DEX_ROW_TS]
      ,[DEX_ROW_ID]
;
SQL SELECT [ACCATNUM]
      ,[ACCATDSC]
      ,[DEX_ROW_TS]
      ,[DEX_ROW_ID]
  FROM [dbo].[GL00102]
;
STORE DATA into [lib://Qlik Data (qlikservice)/$(Company)\GL\Account_Category_Master.qvd] (qvd);
DROP Table DATA;
END SUB;

...

LIB CONNECT TO 'AOW (dts_qlikservice)';
Call GLAccountCategoryMaster('AOW');

.