3 Replies Latest reply: Jun 15, 2017 9:46 AM by Justin Dallas RSS

    Same Load Statement - 3 different Dbs

    Justin Dallas

      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.

        • Re: Same Load Statement - 3 different Dbs
          David Forest

          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
              Justin Dallas

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

              .

            • Re: Same Load Statement - 3 different Dbs
              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.