Qlik Sene Avg for null values

[Tarek2020]

Hi evryone,

can someone help me please with thi issue?

I have a  table with 5 fields. the calculation should be for the null distance values:

If( isnull(distance), calculate the avg of distance where Consignee Zip , Consignor Zip, Consignee Zip Code, Consignor Zip code is the same.

for example in the second row should be the avg of 67 and 12 means 39,5.

Shipment no	Consignee Zip	Consignor Zip	Consignee Zip Code	Consignor Zip code	Distance
1	4	4	23	34	67
2	4	4	23	34	0
3	4	4	23	34	12
4	5	5	6	6	400
5	5	5	6	6	0
6	5	5	6	6	0
7	5	5	6	6	0

[sonkumamon]

I would do it on the script level, for instance:

TempTable:

Load

[Shipment no],

[Consignee Zip],

[Consignor Zip],

[Consignee Zip Code],

[Consignor Zip code]

From...

Where Distance=0;

Left Join

Load

[Consignee Zip],

[Consignor Zip],

[Consignee Zip Code],

[Consignor Zip code],

Distance

From...;

Table:

[Shipment no],

Avg(Distance) as Distance

Resident TempTable

Group By [Shipment no];

And you will get the average of the distance where the other columns are similar for Shipment No with distance of 0.

[Tarek2020]

Hi Sonkumamon,

thank you for your message. Unfortunately it does not working. I am searching for a logic for the frontend.

[sonkumamon]

It is very difficult to achieve in the frontend, and would have a negative impact on the performance. What is not working as expected in my solution?