2 Replies Latest reply: Oct 5, 2014 8:48 AM by Ashfaq Mohammed RSS

    Trim or take out spaces after a field in excel spread sheet coming in to qlikview.

    Alam Nizami

      Hi,

      I have a excel spread sheet which I am pulling project names in from. It comes in through a variable, but I want to fix a potential issue a user can create when updating the excel sheet with more project names.

       

      If there is a space after the project name it duplicates that project name in qlikview.


      I want to write in my extract code to drop any spaces before and after the name entered in the excel rows.

       

      here is my code:

       

       

       

      //Excel file from QLIKVIEW_DROPBOX

      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 from spread sheet.

      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 Dynamic connection string

       

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

       

      Let vDefectsTableName = 'Defects'; // Created table variable, and Defined Table Name

       

       

      //Extract

       

       

      $(vDefectsTableName):

       

       

      // Load everything in to table variable

       

       

      Concatenate

       

       

      LOAD  *;

       

       

      SQL

       

       

      // Get information from td.BUG table in the database and link the excel sheet info with the info in the database with ProjectName.

      SELECT '$(vProjectName)' AS ProjectName, '$(vAdditionDate)' AS AdditionDate,'$(vWorkRequest)' AS WorkRequest, bug.*

       

       

      FROM td.BUG as bug;  /*Defect*/

       

       

      next;

       

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