Fill in blanks with the max value grouped by ID

BonganiButh — Mon, 25 Jan 2021 19:59:35 GMT

How do i go about filling in all the missing values in 'Avg_Cost' with the known max value as seen in the above.

I have tried the below but no luck.

LOAD [request id],
[order id],
line,
[customer ref],
repair_type,
Repair_class,
[order status],
[order stat],
[product id],
Product_Name,
Avg_Cost,
Repair_Age_in_Days,
warehouse_id,
station_id,
ID,
ID_1,
allocated_qty,
qty,
in_bound_qty,
out_bound_qty,
qty_at_repair_vendor,
qty_at_customer,
[assigned name],
[assigned to],
[action group name],
[action group id],
Bytes_Region,
IMO,
IMO_status_id,
IMO_status,
primary_IMO,
Primary_IMO_Status_id,
Primary_IMO_Status,
fr_warehouse_id,
to_warehouse_id,
fExceptions,
RESPONSIBILITY,
ACTION,
Operation,
ID2,
carrier_id,
carrier_ref_no,
Ageing_category,
AgeRank,
Owner,
Tracking_no;

[OpenRepairOrders]:
SELECT "request id",
"order id",
line,
"customer ref",
"repair_type",
"Repair_class",
"order status",
"order stat",
"product id",
"Product_Name",
"Avg_Cost",
"Repair_Age_in_Days",
"warehouse_id",
"station_id",
ID,
"ID_1",
"allocated_qty",
qty,
"in_bound_qty",
"out_bound_qty",
"qty_at_repair_vendor",
"qty_at_customer",
"assigned name",
"assigned to",
"action group name",
"action group id",
"Bytes_Region",
IMO,
"IMO_status_id",
"IMO_status",
"primary_IMO",
"Primary_IMO_Status_id",
"Primary_IMO_Status",
"fr_warehouse_id",
"to_warehouse_id",
fExceptions,
RESPONSIBILITY,
"ACTION",
Operation,
ID2,
"carrier_id",
"carrier_ref_no",
"Ageing_category",
AgeRank,
Owner,
"Tracking_no"
FROM AsteaMSBISDB.dbo.OpenRepairOrders;

LEFT JOIN (OpenRepairOrders)

[MaxCost]:
LOAD [order id],
if(len(trim(Avg_Cost))=null() and [product id]=peek([product id]), Peek(MaxFiled), Alt(Avg_Cost,0)) as MaxAvg,

[product id]
Resident [OpenRepairOrders];

Re: Fill in blanks with the max value grouped by ID

Ksrinivasan — Tue, 26 Jan 2021 04:40:53 GMT

hi,

try this

https://community.qlik.com/t5/New-to-Qlik-Sense/Peek-Function-multiple-rows/td-p/1777234

ksrinivasan

Re: Fill in blanks with the max value grouped by ID

BonganiButh — Tue, 26 Jan 2021 11:49:47 GMT

Thanks @Ksrinivasan This help me resolve the issue.

topic Re: Fill in blanks with the max value grouped by ID in App Development

Fill in blanks with the max value grouped by ID

Re: Fill in blanks with the max value grouped by ID

Re: Fill in blanks with the max value grouped by ID