Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

How to bind 2 tables data and sum from third table

Hi friends,
I have data source in following format:

table "ProjectData":
IDMyProject MyProjectName
1 project 01
1 project 02
...

table "DeliverableData":
IDMyProject IDMyDeliverable IDMyProjectDeliverable MyDeliverableName MyDeliverableStartDate MyDeliverableEndDate
1 1 1 deliverable P01-01 1.1.2010 31.1.2010
1 2 2 deliverable P01-02 15.1.2010 15.2.2010
...

table "DeliverableRealWorkingActivity":
IDMyProject IDMyDeliverable IDMyProjectDeliverable IDWorkingJob WorkingDate DurationOfWorking MyDeliverableSource
1 1 1 1 1.1.2010 45 JC
1 1 1 2 5.1.2010 60 JC
1 1 1 3 3.1.2010 100 AB
1 1 1 4 4.1.2010 120 AB
...

I would like to build following data:
IDMyProject MyProjectName IDMyDeliverable IDMyProjectDeliverable MyDeliverableName MyDeliverableStartDate MyDeliverableEndDate SumOfWorkingDurationForAllSources
1 project 01 1 1 deliverable P01-01 1.1.2010 31.1.2010 325
1 project 01 2 2 deliverable P01-02 15.1.2010 15.2.2010 ...
...

where last column "SumOfWorkingDurationForAllSources" would be calculated sum of all rows (values "DurationOfWorking") of table "DeliverableRealWorkingActivity" for each "IDMyProject" and "IDMyDeliverable".

Does anybody have some idea, how can I write script to load these data and create mentioned structure of data?

very thanks

Tags (1)
1 Reply
Not applicable

How to bind 2 tables data and sum from third table

hi friends,

voila! I find solution! Loading script may be following:

TempCostsProjectData:
LOAD IDMyProject,
MyProjectName
FROM Test.xls (biff, embedded labels, table is ProjectData$);
JOIN LOAD
IDMyProject,
IDMyDeliverable,
IDMyProjectDeliverable,
MyDeliverableName,
MyDeliverableStartDate,
MyDeliverableEndDate
FROM Test.xls (biff, embedded labels, table is DeliverableData$);
JOIN LOAD
IDMyProject,
IDMyDeliverable,
IDMyProjectDeliverable,
Sum(DurationOfWorking) AS SumDurationOfWorking
FROM Test.xls (biff, embedded labels, table is DeliverableRealWorkingActivity$)
GROUP BY IDMyProject, IDMyDeliverable, IDMyProjectDeliverable;

CostsProjectData:
LOAD IDMyProject,
MyProjectName,
IDMyDeliverable,
IDMyProjectDeliverable,
MyDeliverableName,
MyDeliverableStartDate,
MyDeliverableEndDate,
SumDurationOfWorking
Resident TempCostsProjectData;