topic Re: Converting MS Query to Load Script in QlikView

Converting MS Query to Load Script

Tue, 04 Feb 2014 09:07:09 GMT

Hi everyone,

I have got a query in Excel to fetch information from our timereporting system. Is there an easy way to convert this query into a load script for Qlik?

I am have also added a few fields manually in Excel with formulas. How can I get this into the load script. For example in the query below, I am fetching these two fields:

MSP_EpmProject_UserView."Main Project no"

MSP_EpmTask_UserView."Project no"

Then, I have created a field called "Task Project" where I bring in Project no if that is filled in, if not, I use Main Project no.

=TRIM(IF([@[Project no]]="";[@[Main Project no]];[@[Project no]]))

SELECT MSP_EpmAssignmentByDay.TimeByDay, MSP_EpmAssignmentByDay.AssignmentActualWork, MSP_EpmResource_UserView.ResourceName, MSP_EpmResource_UserView."AX-ID", MSP_EpmTask_UserView.TaskName, MSP_EpmProject_UserView.ProjectName, MSP_EpmProject_UserView."Main Project no", MSP_EpmResource_UserView.RBS, MSP_EpmTask_UserView."Activity Type", MSP_EpmTask_UserView."Project no", MSP_EpmTask_UserView."Invoice Type", MSP_EpmTask_UserView.TaskName

FROM "epm-2010-reporting".dbo.MSP_EpmAssignment MSP_EpmAssignment, "epm-2010-reporting".dbo.MSP_EpmAssignmentByDay MSP_EpmAssignmentByDay, "epm-2010-reporting".dbo.MSP_EpmProject_UserView MSP_EpmProject_UserView, "epm-2010-reporting".dbo.MSP_EpmResource_UserView MSP_EpmResource_UserView, "epm-2010-reporting".dbo.MSP_EpmTask_UserView MSP_EpmTask_UserView

WHERE MSP_EpmAssignment.AssignmentUID = MSP_EpmAssignmentByDay.AssignmentUID AND MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID AND MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignmentByDay.ProjectUID AND MSP_EpmAssignment.TaskUID = MSP_EpmTask_UserView.TaskUID AND (MSP_EpmAssignmentByDay.AssignmentActualWork>$0) AND (MSP_EpmProject_UserView."Internal Project"='No')

I started working on this, but cannot get it to work.

OLEDB CONNECT TO [Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=epm-2010-reporting;Data Source=hbg-sql2008;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=HBG-THORH;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False];

LOAD month(TimeByDay) as Month,
year(TimeByDay) as [Fiscal Year],
AssignmentActualWork as Hours,
AssignmentUID,
ProjectUID;
SQL SELECT *
FROM "epm-2010-reporting".dbo.MSP_EpmAssignmentByDay;

LOAD ProjectName as EPMProjectName,
"Main Project no" as PROJID,
ProjectUID;
SQL SELECT *
FROM "epm-2010-reporting".dbo.MSP_EpmProject_UserView;

LOAD ProjectUID,
TaskUID,
AssignmentUID,
ResourceUID;
SQL SELECT *
FROM "epm-2010-reporting".dbo.MSP_EpmAssignment;

LOAD ResourceName,
"AX-ID",
ResourceUID,
RBS;
SQL SELECT *
FROM "epm-2010-reporting".dbo.MSP_EpmResource_UserView;

LOAD TaskUID,
     TaskName,
     "Project no" as PROJID,
     "Activity Type" as Activity;
SQL SELECT *
FROM "epm-2010-reporting".dbo."MSP_EpmTask_UserView";

Re: Converting MS Query to Load Script

hic — Tue, 04 Feb 2014 09:15:42 GMT

I think your approach is correct. But it is impossible for us to say what is going wrong since we do not have the data or the app.

Check your data model (ctrl-T):

Make sure that you have loaded the keys between the tables.
Make sure that you do not have any synthetic keys (usually a sign that you have fields that link tables, but not are keys)

HIC

Re: Converting MS Query to Load Script

Tue, 04 Feb 2014 10:10:03 GMT

Thanks for your reply Henric.

This is how it looks.

Also, any suggestions on how to solve:

MSP_EpmProject_UserView."Main Project no"

MSP_EpmTask_UserView."Project no"

Then, I have created a field called "Task Project" where I bring in Project no if that is filled in, if not, I use Main Project no.

=TRIM(IF([@[Project no]]="";[@[Main Project no]];[@[Project no]]))

Re: Converting MS Query to Load Script

hic — Tue, 04 Feb 2014 10:21:02 GMT

You obviously have a problem in your data model, perhaps too many keys. Do the following:

Switch to "Source Table View" in the table viewer. Now you can see what you really have loaded.
Rename the tables (by adding a label in front of the Load in the script). This will not change the data model, but it will help you think. If a table has exactly one record per project, the table should be called "Projects". The same for "Assignments" and "Tasks". If you don't know what each record represents, you need to figure it out.
Look at the data model and think. Ask questions like: Are the keys in the right tables? Are there any redundant keys? Are the tables linked correctly? Can there be more than one Assignment per Task? Can a Task belong to several Projects?

Without knowing the data, I cannot answer these questions.

HIC