Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
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!
I am glad I am able to help