How can get I get relational data from another table and calculate the average in Qlikview?

Question

I have relationship with 2 tables

Table 1 - Process

Table 2 - Process History

Here the relationship is Id(Process table) and ProcessId(Process history table) I want to calculate the Average Networking days of all the processes.

For eg:

nwd = 0;
count = 0;
if(Process.Id = ProcessHistory.ProcessId && ProcessHistory.Status='Status 3') {
  nwd += NWD(Process.CreatedOn, ProcessHistory.CreatedOn);
  count++;
}

Expected result AverageNWD = nwd/count;

How can we achieve this?

Answer 1

In the script:

Using the script below will add a new field to the Process table - NetWorkingDays. This field will contain the working days for each project (Id). With this field in the dataset will be easier to calculate the average in the UI (something like sum(NetWorkingDays) / count(distinct Id)

Process:
Load * Inline [
Id, Name    , CretedOn
1,  Process1, 2019-04-02
2,  Process2, 2019-04-05
3,  Process3, 2019-05-02
4,  Process4, 2019-06-02
];


ProcessHistory:
Load 
  Id        as ProcessHistoryId,
  ProcessId as Id,
  Status,
  CreatedOn as ProcessHistoryCreatedOn
;
Load * Inline [
Id, ProcessId, Status  , CreatedOn
1,  1,         Status 1, 2019-04-02
2,  1,         Status 2, 2019-04-02
3,  1,         Status 3, 2019-04-04
4,  2,         Status 1, 2019-04-05
5,  2,         Status 3, 2019-04-06
6,  3,         Status 1, 2019-05-07
7,  3,         Status 3, 2019-05-09
8,  4,         Status 1, 2019-06-02
9,  4,         Status 2, 2019-06-04
10, 4,         Status 3, 2019-06-07
];


TempTable:
Load
  Id,
  min(CretedOn) as MinCreatedOn
Resident
  Process
Group By
  Id
;

join (TempTable)

Load
  Id,
  max(ProcessHistoryCreatedOn) as MaxCreatedOn
Resident
  ProcessHistory
Where
  Status = 'Status 3'
Group By
  Id
;


NetWorkingDaysData:
Load
  Id,
  NetWorkDays(MinCreatedOn, MaxCreatedOn) as NetWorkingDays 
Resident
  TempTable
;

Drop Table TempTable;

The last part of the script (from inside out):

Create temporary table to calculate min(CreatedOn) from Process table and max(ProcessHistoryCreatedOn) from ProcessHistory table. ProcessHistory is also filtered to include only records where Status = 'Status 3' (both tables are aggregated per Id)

TempTable:
Load
  Id,
  min(CretedOn) as MinCreatedOn
Resident
  Process
Group By
  Id
;

join (TempTable)

Load
  Id,
  max(ProcessHistoryCreatedOn) as MaxCreatedOn
Resident
  ProcessHistory
Where
  Status = 'Status 3'
Group By
  Id
;

Once the temp table is created we can create the final table that in which we will calculate the number of net working days using the NetWorkDays function. The NetWorkingDaysData table will have only two fields - Id and NetWorkingDays

NetWorkingDaysData:
Load
  Id,
  NetWorkDays(MinCreatedOn, MaxCreatedOn) as NetWorkingDays 
Resident
  TempTable
;

And the final step is to drop the TempTable - its no longer required

In the UI:

The same result can be achieved in the UI using the expression below. Just bear in mind that the UI approach might lead to higher resource consumption! Since all the calculations are on-the-fly (depends how big your dataset is)

avg(
  Aggr(
    NetWorkDays( min(ProcessHistoryCreatedOn) , max( {< Status = {'Status 3'} >} ProcessHistoryCreatedOn) )
  , Id)
)