Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Business_Units_Mapeo :
Mapping Load ID,
Desc
From Business_Units _Table;
Aviod statistical boxes and try creating straight table instead with Business_units, Estatus as dimensions and a sum(ProjectCount) as an expression.
Regards.
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.
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;
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.