Hello, Here is detail. Can someone please help me with script to create a fact table. Would really appreciate. I am totally new with Qlikview scripting.
Project_Detail: // (first - SQL view)
SQL select distinct
a.ProjectUID,
a.ProjectName,
a.ProjectAuthorName,
a.ProjectCost,
b.Start_Date,
b.Finish_Date,
b.Created_Date,
from
dbo.Table1 a inner join
dbo.Table2 b on a.ProjectUID = b.PROJ_UID
ProjectCountbystatusbybusiness: // (Second - SQL View)
SQL Select
'ProjectCount' = CASE
when c.LT_VALUE_TEXT = 'Cancelled' then COUNT(c.VALUE)
when c.LT_VALUE_TEXT = 'Close' then COUNT(c.VALUE)
when c.LT_VALUE_TEXT = 'Complete' then COUNT(c.VALUE)
ELSE '' END,
a.[Business Unit] as Business_Units,
c.VALUE as Project_Status
from
dbo.Table1 a inner join
dbo.Table2 b on a.ProjectUID = b.PROJ_UID inner join
dbo.Table3 c on b.UID = c.UID
group by a.[Business Unit], c.VALUE
BusinessUnits_Lookup: // (first - lookup table)
SQL select distinct [Business Unit] as Business_Units,
from dbo.Table1;
ProjectStatus_Lookup: // (second - lookup table)
Load * Inline [
Project_Status
Cancelled
Close
Complete];
In the above four tables, I don't know how to create a fact table because Project_Detail has no fields to link with other tables. ProjectCountbystatusbybusiness can link easily with other two lookup tables. I am trying to do something like following but unsuccessful, need help here: I thought I need a fact table to accomplish the task and trying to create some script. Please assist.
Projects:
LOAD
ProjectUID, ProjectName, ProjectAuthorName, ProjectCost, Start_Date, Finish_Date, Created_Date,
applymap('BusinessUnits_Lookup ',Business_Units),
applymap('ProjectStatus_Lookup ',Project_Status)
resident Project_Detail;
LEFT JOIN
LOAD
ProjectCount, Business_Units, Project_Status
resident ProjectCountbystatusbybusiness;
