blocks|key|2287387|text|脚本中的：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2287388|使用下面的脚本将向Process表中添加一个新字段-+NetWorkingDays。此字段将包含每个项目的工作日(Id)。在数据集中使用此字段将更易于在UI中计算平均值(类似于sum(NetWorkingDays)+/+count(distinct+Id)|CODE|2287389|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;|code-block|syntax|javascript|2287390|脚本的最后一部分(从里到外)：|2287391|创建临时表，从Process表计算min(CreatedOn)，从ProcessHistory表计算max(ProcessHistoryCreatedOn)。还会对ProcessHistory进行过滤，以仅包括其中包含Status+=+'Status+3'的记录(两个表都是按Id聚合的)|2287392|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
;|2287393|一旦创建了临时表，我们就可以创建最终的表，在该表中，我们将使用NetWorkDays函数计算净工作日的天数。NetWorkingDaysData表将只有两个字段-+Id和NetWorkingDays|2287394|NetWorkingDaysData:
Load
++Id,
++NetWorkDays(MinCreatedOn,+MaxCreatedOn)+as+NetWorkingDays+
Resident
++TempTable
;|2287395|最后一步是删除TempTable+-不再需要它|2287396|UI中的：|2287397|使用下面的表达式可以在UI中实现相同的结果。只需记住UI方法可能会导致更高的资源消耗！因为所有的计算都是动态的(取决于你的数据集有多大)|2287398|avg(
++Aggr(
++++NetWorkDays(+min(ProcessHistoryCreatedOn)+,+max(+{<+Status+=+{'Status+3'}+>}+ProcessHistoryCreatedOn)+)
++,+Id)
)|2287399|entityMap|0|LINK|mutability|MUTABLE|url|https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/DateAndTimeFunctions/networkdays.htm^0|4|1|0|9|7|R|E|1L|2|2G|14|0|0|0|7|7|H|E|X|E|1E|S|2A|E|31|J|3U|2|0|0|1I|I|2A|2|2D|E|V|B|0|0|0|7|9|0|4|1|0|M|L|0|0^^$0|@$1|2|3|4|5|6|7|1D|8|@$9|1E|A|1F|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1G|8|@$9|1H|A|1I|B|H]|$9|1J|A|1K|B|H]|$9|1L|A|1M|B|H]|$9|1N|A|1O|B|H]]|D|@]|E|$]]|$1|I|3|J|5|K|7|1P|8|@]|D|@]|E|$L|M]]|$1|N|3|O|5|6|7|1Q|8|@]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1R|8|@$9|1S|A|1T|B|H]|$9|1U|A|1V|B|H]|$9|1W|A|1X|B|H]|$9|1Y|A|1Z|B|H]|$9|20|A|21|B|H]|$9|22|A|23|B|H]|$9|24|A|25|B|H]]|D|@]|E|$]]|$1|R|3|S|5|K|7|26|8|@]|D|@]|E|$L|M]]|$1|T|3|U|5|6|7|27|8|@$9|28|A|29|B|H]|$9|2A|A|2B|B|H]|$9|2C|A|2D|B|H]]|D|@$9|2E|A|2F|1|2G]]|E|$]]|$1|V|3|W|5|K|7|2H|8|@]|D|@]|E|$L|M]]|$1|X|3|Y|5|6|7|2I|8|@$9|2J|A|2K|B|H]]|D|@]|E|$]]|$1|Z|3|10|5|6|7|2L|8|@$9|2M|A|2N|B|C]]|D|@]|E|$]]|$1|11|3|12|5|6|7|2O|8|@$9|2P|A|2Q|B|C]]|D|@]|E|$]]|$1|13|3|14|5|K|7|2R|8|@]|D|@]|E|$L|M]]|$1|15|3|-4|5|6|7|2S|8|@]|D|@]|E|$]]]|16|$17|$5|18|19|1A|E|$1B|1C]]]]

In the script: 

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

<pre><code>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;
</code></pre>

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

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

<pre><code>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
;
</code></pre>

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 <a href="https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/DateAndTimeFunctions/networkdays.htm" rel="nofollow noreferrer">NetWorkDays</a> function. The <code>NetWorkingDaysData</code> table will have only two fields - <code>Id</code> and <code>NetWorkingDays</code> 

<pre><code>NetWorkingDaysData:
Load
 Id,
 NetWorkDays(MinCreatedOn, MaxCreatedOn) as NetWorkingDays 
Resident
 TempTable
;
</code></pre>

And the final step is to drop the <code>TempTable</code> - 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)

<pre><code>avg(
 Aggr(
 NetWorkDays( min(ProcessHistoryCreatedOn) , max( {&lt; Status = {'Status 3'} &gt;} ProcessHistoryCreatedOn) )
 , Id)
)
</code></pre>

I have relationship with 2 tables

Table 1 - Process

<a href="https://i.stack.imgur.com/QFcOv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QFcOv.png" alt="enter image description here"></a>

Table 2 - Process History

<a href="https://i.stack.imgur.com/UH268.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UH268.png" alt="enter image description here"></a>

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: 

<pre><code>nwd = 0;
count = 0;
if(Process.Id = ProcessHistory.ProcessId &amp;&amp; ProcessHistory.Status='Status 3') {
 nwd += NWD(Process.CreatedOn, ProcessHistory.CreatedOn);
 count++;
}
</code></pre>

Expected result AverageNWD = nwd/count;

How can we achieve this?

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

我和2个表有关系表1-流程​​表2-进程历史记录​​这里的关系是Id(进程表)和ProcessId(进程历史表)，我想要计算所有进程的平均联网天数。例如：nwd = 0;count = 0;if(Process.Id = ProcessHistory.ProcessId && ProcessHistory.Status...

问如何从另一个表中获取关系数据并在Qlikview中计算平均值？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从另一个表中获取关系数据并在Qlikview中计算平均值？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何从另一个表中获取关系数据并在Qlikview中计算平均值？
EN