Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

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

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

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

1 Solution

Accepted Solutions

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

Hi,

Try like this

Trim(YourField) as NewField

Regards

ASHFAQ

2 Replies
nagaiank
Valued Contributor III

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

Did you try using the following script?

Defects:

Load Trim(ProjectName) as ProjectName, DatabaseName, AdditionDate, WorkRequest;

LOAD

  ProjectName,

     DatabaseName,

     AdditionDate,

     WorkRequest

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE Len(Trim(ProjectName)) > 0;

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

Hi,

Try like this

Trim(YourField) as NewField

Regards

ASHFAQ

Community Browser