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


      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









      [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









      // Load everything in to table variable






      LOAD  *;






      // 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*/





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