3 Replies Latest reply: Aug 28, 2014 12:14 PM by Alam Nizami RSS

    Bring in data unique to project with for Loop, and variable.

    Alam Nizami

      Hi,

       

      I need some help. I am trying to bring in data from multiple databases which I have done using the following code. The database names are taken from a excel sheet and a variable is used in the connection string. That’s all fine.

       

      Where I am running in to trouble is for example.

       

      Each DB has a table called td.Bug and in each they have the same columns.

      Example: Column 1, Column 2, Column 3.

       

      I pull the info using a variable and a “for loop”. The issue is in some tables, Column 1 can = Developer name, but in another table in another DB but same column name “Column 1” might = Date.

       

      Right now when I bring it in, Qlikview will bring in “Column 1” and it will have Developer names and Dates, but I need them to be separate because different projects use different information.

       

      (Not sure why this was not thought of when making the DB table for each project, I’m just trying to clean it up on the front end.)

       

      Is there anything I can do in my current code that would help in getting the result I want?

       

      BTW, I am a beginner at coding and Qlikview.

       

      Thanks,

       

      Code so far----------v

       

      Defects:
      LOAD ProjectName,
      DatabaseName,
      AdditionDate,
      WorkRequest
      FROM
      [H:\Information Technology\QLIKVIEW_DROPBOX\QCProjects.xlsx]
      (
      ooxml, embedded labels, table is Sheet1)
      WHERE Not IsNull(ProjectName);


      // fill variable with field values row by row
      For i = 0 To NoOfRows('Defects')-1
      let vDatabaseName=peek('DatabaseName', $(i),'Defects');
      let vProjectName=peek('ProjectName', $(i),'Defects');
      let vAdditionDate=peek('AdditionDate', $(i),'Defects');
      let vWorkRequest=peek('WorkRequest', $(i), 'Defects');
      trace $(vDatabaseName) $(vProjectName) $(vAdditionDate) $(vWorkRequest);

      // .......... use connection string
       
      OLEDB CONNECT32 TO [Provider=SQLOLED6.1;Persist Security Info=True;User ID=BI_QLIKVIEW;Initial Catalog=$(vDatabaseName);Data Source=SDBDCA1P;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=OPT0;Use Encryption for Data=False;Tag with column collation when possible=False] (XPASsword is);


      Let vDefectsTableName = 'Defects'; // Define Table Name

      //Extract

      $(vDefectsTableName):

      // Load everything in to table variable

      Concatenate

      LOAD  *;

      SQL

      // Get information from Table in database
      SELECT '$(vProjectName)' AS ProjectName,'$(vAdditionDate)' AS AdditionDate,'$(vWorkRequest)' AS WorkRequest, bug.*

      FROM td.BUG as bug;

      /*Defect*/

      next;



      //Exit Script;

      STORE $(vDefectsTableName) INTO ..\..\02_QVDs\IT\IT_QC_Defects.qvd (QVD);