Working with Aggregates

[#1]

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.

[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.

[#3]

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.

[#4]

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;

[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.