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.
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.
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;
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:
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.