6 Replies Latest reply: Dec 19, 2012 4:38 PM by steve peroni RSS

    same table in several databases

    steve peroni

      Dear all,

      I've the same table stored into several db and I need to gather all data into one table. Is it possible to create a loop, changing the odbc connection ?

      How can I cope this scenario ?

      Help please.

       

      Thanks in advance

        • Re: same table in several databases

          Hi,

           

          I see several questions so let me break them down and respond.

           

          1. Is it possible to create a loop

          Yes, you can use VBSCRIPT loops in Qlikview

           

          // Load all excelsheets with a loop

          // DB is the variable table

          // DBLIST is the table containing all the databases.

          for each DB in DBLIST

              SQL SELECT FROM $(DB);

          Next ExcelFile;


          2. Is it possible to change the odbc connection

          Yes, see the example below.

           

          3. How can I cope with this scenario.


          The best way to handle this is to load your data from different sources and directly save them in a

          qvd. You can then use the qvd's as you see fit.

           

          Assuming the tables are in the same format and position:

           

          Example:

           

          CONNECT TO odbc1;

          odbc1:

          SQL SELECT * FROM A;

          STORE * FROM odbc1 INTO odbc1.qvd;

          DROP TABLE odbc1;

           

          CONNECT TO odbc2;

          odbc2:

          SQL SELECT * FROM A;

          STORE * FROM odbc1 INTO odbc2.qvd;

          DROP TABLE odbc2;

           

          A:

          LOAD * FROM odbc1.qvd (qvd);

          concatenate

          LOAD * FROM odbc2.qvd (qvd);

           

          Let me know if you need more help to construct this

           

          Dion Verbeke

           

          Certified QV11 Designer, Developer, Administrator

          • Re: same table in several databases

            Hi,

            I've the same problem.

            Do you have any further ? I've more than 20 db and the example above is not the best

             

             

            • Re: same table in several databases
              Ralf Becher

              Hi bibopipo,

               

              you can do this in the load script with a for..each loop. Lets say you have 3 source databases with the same table:

               

              FOR EACH vConnectStr IN 'CONN1', 'CONN2', 'CONN3'

                ODBC CONNECT TO [$(vConnectStr)];

                qvtable:

                SELECT col1, col2, ..., colx FROM table;

              NEXT

               

              - Ralf

                • Re: same table in several databases
                  steve peroni

                  Dear all,

                  I solved (I hope) in  this way:

                   

                  LET Number_of_records = NoOfRows('MytableList');

                   

                  FOR i=0 to $(Number_of_records)-1

                  let DBName=peek('NOME_DB',$(i),'MytableList');

                  let Firsttime = 0;

                   

                  ODBC CONNECT TO $(DBName) (XUserId is MyUser, MyPassword is VaaZSRZMULZGXUJd);

                   

                  If $(Firsttime) = 0 THEN

                  Mytable0:

                  LOAD

                       ......

                       ......   

                  FROM

                  E:\myfolder\my_data\t130tl$(DBName).qvd

                  (qvd);

                   

                  Mytable2:

                  LOAD

                       ............

                       ............    

                    

                  FROM

                  E:\myfolder\my_data\t135tl$(DBName).qvd

                  (qvd);

                   

                   

                   

                  set Firsttime = 1;

                  ELSE

                   

                  Mytable1:

                  concatenate(mytable0)

                  LOAD

                       ..............

                       ..............

                      

                  FROM

                  E:\myfolder\my_data\t130tl$(DBName).qvd

                  (qvd);

                   

                  Mytable3:

                  concatenate(Mytable2)

                  LOAD

                       .............

                       ............

                   

                  FROM

                  E:\myfolder\my_data\t135tl$(DBName).qvd

                  (qvd);

                   

                  ENDIF

                   

                  next

                   

                  Now I've two tables , Mytable_0 and Mytable2 with all records.

                  Thanks to everyone has spent time to help me.