Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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";
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):
HIC
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]]))
You obviously have a problem in your data model, perhaps too many keys. Do the following:
Without knowing the data, I cannot answer these questions.
HIC