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

Replace Null values with Max value based on an aggregation

mrthomasshelby — Wed, 07 Mar 2018 07:07:25 GMT

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‌

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

niclaz79 — Wed, 07 Mar 2018 08:13:29 GMT

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

sunny_talwar — Wed, 07 Mar 2018 10:16:08 GMT

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;

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

mrthomasshelby — Wed, 07 Mar 2018 11:14:08 GMT

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

sunny_talwar — Wed, 07 Mar 2018 11:15:36 GMT

I am glad I am able to help