4 Replies Latest reply: Apr 23, 2010 2:13 PM by Karl Pover RSS

    Working with Aggregates

    Khanrohila

      Hello,

      I m new to Qlikview.

      I have few questions.

      Working on a dashboard with SQL backend containg two lookup tables, one detail table and one summary view.

      TableName Colums

      A Project_Status (lookup table)

      B Business_Units (lookup table)

      C ProjectID, StartDate, EndDate, ProjectName, Business_Units, Project_Status (Detail table)

      D Project_Status, Business_units, ProjectCount (It's a SQL view where I count the all project status for each Business Units)

      Q 1- How can I create a star schema based on above tables. Please advise with scripting if possible.

      Q2 - In order to create a sum of all project count in a statistical box what should I do?

      For example if I have a Project_Status as Complete, Failed, Cancelled and Business_units as Unit1, Unit2, Unit3 and Count of them in able D are;:

      3 Unit1 Cancelled

      2 Unit2 Cancelled

      7 Unit3 Cancelled

      23 Unit1 Complete

      22 Unit2 Complete

      27 Unit3 Complete

      13 Unit1 Failed

      12 Unit2 Failed

      17 Unit3 Failed

      How can I show the Complete_Project Sum, Failed_Project Sum, Cancelled_Project Sum in three different statistical boxes.

      Or any other logic you would recommend.

      Thanks in advance.

        • Working with Aggregates
          Karl Pover

          Hello Khanrohilia,

          A star sheme would not be necessary here since you can put everything easily in one table with some code like below:

           

          Project_Status_Mapeo:
          Mapping Load ID,
          Desc
          From Project_Status_Table;

          Business_Units_Mapeo :
          Mapping Load ID,
          Desc
          From Business_Units _Table;

          Projects:
          Load ProjectID, StartDate, EndDate, ProjectName, Business_Units, Project_Status, applymap('Business_Units_Mapeo ',Business_Units) as Business_Units_Name, applymap('Business_Units_Mapeo ',Project_Status) as Project_Status_Name from Detail_table;
          LEFT JOIN
          Project_Status, Business_units, ProjectCount from view;

           

          Aviod statistical boxes and try creating straight table instead with Business_units, Estatus as dimensions and a sum(ProjectCount) as an expression.

          Regards.

            • Working with Aggregates
              Khanrohila

              Hello Karl,

              Thanks a lot for quick response. I really appreciate.

              I think I need little more help here, I can't write this script exactally as it is as i forget to mention, the summary view i have created in Qlikview script as SQL Select rather then pulling from SQL database. Can you please tell me what to do?

              Can you also please inform me about some qlikview book or site or resource, from where I can learn all the loading methods and join logic and syntax for scripting. Getting started is not a good help.

                • Working with Aggregates
                  Khanrohila

                  Hello, Here is further detail. Can Karl or someone please help me with script to create a final 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 link Project_Detail: table in my data diagram with other three. ProjectCountbystatusbybusiness can link easily with other two lookup tables. Now based on karl's input I am trying to do something like following but unsuccessful, need help here: I thought I need a star schema but he suggests it's not necessary here since I can put everything easily in one table with some code like below:

                  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;





                    • Working with Aggregates
                      Karl Pover

                      Hi Khanrohila,

                      Thanks for sending more details. First, you don't need ProjectCountbystatusbybusiness because QlikView can do that in the graphic interfase. Actually in QlikView you will want to avoid all the sums, max, counts and group by that are so common in SQL. QlikView will do the group by and count on the graphic side and that's what makes it so dynamic, flexible and easy to use.

                      Project_Detail is the main table, but it needs to have a status ID or a business unit ID to be able to map the value that corresponds to the project in the lookup table. The lookup table should have 2 columns (ID and Desc).

                      There isn't a book about QlikView that I know of, but you will find online training in QlikCommunity even though it might be a little slow and not so advanced you might want to review it so that you have a solid foundation.

                      Regards.