0 Replies Latest reply: Apr 23, 2010 12:30 AM by Khanrohila RSS

    Please Help. How to create a script for fact table.

    Khanrohila

      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;