Bring in data unique to project with for Loop, and variable.

[#1]

Hi,

I need some help. I am trying to bring in data from multiple databases which I have done using the following code. The database names are taken from a excel sheet and a variable is used in the connection string. That’s all fine.

Where I am running in to trouble is for example.

Each DB has a table called td.Bug and in each they have the same columns.

Example: Column 1, Column 2, Column 3.

I pull the info using a variable and a “for loop”. The issue is in some tables, Column 1 can = Developer name, but in another table in another DB but same column name “Column 1” might = Date.

Right now when I bring it in, Qlikview will bring in “Column 1” and it will have Developer names and Dates, but I need them to be separate because different projects use different information.

(Not sure why this was not thought of when making the DB table for each project, I’m just trying to clean it up on the front end.)

Is there anything I can do in my current code that would help in getting the result I want?

BTW, I am a beginner at coding and Qlikview.

Thanks,

Code so far----------v

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
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 connection string
 
OLEDB CONNECT32 TO [Provider=SQLOLED6.1;Persist Security Info=True;User ID=BI_QLIKVIEW;Initial Catalog=$(vDatabaseName);Data Source=SDBDCA1P;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=OPT0;Use Encryption for Data=False;Tag with column collation when possible=False] (XPASsword is);


Let vDefectsTableName = 'Defects'; // Define Table Name

//Extract

$(vDefectsTableName):

// Load everything in to table variable

Concatenate

LOAD  *;

SQL

// Get information from Table in database
SELECT '$(vProjectName)' AS ProjectName,'$(vAdditionDate)' AS AdditionDate,'$(vWorkRequest)' AS WorkRequest, bug.*

FROM td.BUG as bug;

/*Defect*/

next;



//Exit Script;

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

[MarcoWedel · Accepted solution]

Hi,

instead of defining static alias names in your SQL statement use variables for the aliases also and define them in your QCProjects.xlsx.

Something like:

SELECT $(vOrigName) as $(vAliasName)

hope this helps

regards

Marco

[#3]

Hi Marco,

Thanks, but I do not know what you mean. You are saying create a variable for original name of field and give it a variable for Alias. But do not do it in the SQL? I think you are suggesting to do it in the excel sheet, not sure how to do it in there.

[#4]

In the SQL database I can run this query below and it gets me the labels for each column, names that should of been used but they are kept in there separately for some reason. Is there a way I can use this in Qlikview and bring in the actual names. I want to use that to give the columns that have same name in db but different data to be separate columns in qlikview.

SELECT [SF_COLUMN_NAME]

      ,'as ' + Replace([SF_USER_LABEL], ' ', '_') + ',' as user_label

       FROM [td].[SYSTEM_FIELD]

       where sf_table_name = 'bug'

       and sf_user_label is not null)