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