Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor

Same Load Statement - 3 different Dbs

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.

1 Solution

Accepted Solutions
dwforest
Valued Contributor

Re: Same Load Statement - 3 different Dbs

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


3 Replies
dwforest
Valued Contributor

Re: Same Load Statement - 3 different Dbs

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


Re: Same Load Statement - 3 different Dbs

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
Valued Contributor

Re: Same Load Statement - 3 different Dbs

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

.

Community Browser