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

Mon, 26 Jan 2026 18:19:17 GMT

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

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

nagaiank — Fri, 03 Oct 2014 22:26:31 GMT

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.

ashfaq_haseeb — Sun, 05 Oct 2014 12:48:32 GMT

Hi,

Try like this

Trim(YourField) as NewField

Regards

ASHFAQ

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

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

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

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