Replace Null values with Max value based on an aggregation

[mrthomasshelby]

Hello,

I have a data frame as below:

Vendor	LName	Q.P.
A	L1	121
A	L2	-
A	L3	102
B	L1	-
B	L2	39
C	L2	125
C	L3	67
D	L1	120
D	L3	-
E	L3	-

Now what I would like to do is add a part in the script which will fill in the missing or null values in the column 'Q.P' with the maximum value of Q.P within that LName. For example, we can see that Q.P for Vendor A in L2 is missing. So the value I want to assign to this particular cell is the maximum Q.P in L2s from all the vendors. So in this case, we see that the max Q.P in L2 is 125 (from Supplier 'C'). So the second row of 'Q.P' will now have the value 125. Similarly, Vendor B for L1 will have a Q.P of 121 and so on.

For now, I have marked my missing values rows with another column with a flag (A.P. if null, Q.P. if not):

T3:

Load *,

if(IsNull([Q.P.]),'A.P','Q.P') as [A.P./Q.P.],

Resident T2;

Drop Table T2;

Please help me write the statement for fulfilling this. Thanks in advance! stalwar1‌

[niclaz79]

Hi,

Try the below (hard to test without the actual data set) which aggregates QP on Vendor and LName if QP is null.

T3:

Load

     Vendor,

     LName

     if(IsNull([Q.P.]),Max([Q.P.]), [Q.P.]) as [A.P./Q.P.],

Resident T2

Group by Vendor, LName;

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD Vendor,

    LName,

    If(Q.P. = '-', Null(), Q.P.) as Q.P.

FROM

[https://community.qlik.com/thread/293873]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (Table)

LOAD LName,

Max(Q.P.) as MaxQP

Resident Table

Group By LName;

FinalTable:

LOAD Vendor,

LName,

Alt(Q.P., MaxQP) as Q.P.

Resident Table;

DROP Table Table;

[mrthomasshelby]

Hi Sunny,

This is perfect. Just found out about the function 'Alt'. Seems very useful while doing such missing data replacements while preparing the data. I sincerely thank you for taking the time and helping the community learn and grow! Much appreciated!

[sunny_talwar]

I am glad I am able to help