Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

mrthomasshelby
Contributor III

Replace Null values with Max value based on an aggregation

Hello,

I have a data frame as below:

VendorLNameQ.P.
AL1121
AL2-
AL3102
BL1-
BL239
CL2125
CL367
DL1120
DL3-
EL3-

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

1 Solution

Accepted Solutions

Re: Replace Null values with Max value based on an aggregation

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;

Capture.PNG

View solution in original post

4 Replies
Highlighted
Partner
Partner

Re: Replace Null values with Max value based on an aggregation

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;

Re: Replace Null values with Max value based on an aggregation

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;

Capture.PNG

View solution in original post

mrthomasshelby
Contributor III

Re: Replace Null values with Max value based on an aggregation

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!

Re: Replace Null values with Max value based on an aggregation

I am glad I am able to help