Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I'm new to Qlik so apologies if I use the wrong terms. I have the following table with missing values
and I would like to fill in the empty values with the group median so I would get this
I would like to do all this on the data load editor page. So far, I've tried
If( Len(Trim(Value)), Value, Median(Total <"Group"> Value) )
but this doesn't work. I guess this is due to the "<>" symbols. Is there anyways to make this work?
@ihuynhi mayebe like :
Data:
load * inline [
Date,Group,Value
1.1.2021,A,5
2.1.2021,A,6
3.1.2021,A,7
4.1.2021,B,1
5.1.2021,B,
6.1.2021,B,1
7.1.2021,C,
8.1.2021,C,4
9.1.2021,C,5
10.1.2021,C,1
];
left join
load Group,median(Value) as Median resident Data group by Group;
output:
noconcatenate
load Date,Group,if(len(trim(Value))=0,Median,Value) as Value resident Data;
drop table Data;
output:
@ihuynhi mayebe like :
Data:
load * inline [
Date,Group,Value
1.1.2021,A,5
2.1.2021,A,6
3.1.2021,A,7
4.1.2021,B,1
5.1.2021,B,
6.1.2021,B,1
7.1.2021,C,
8.1.2021,C,4
9.1.2021,C,5
10.1.2021,C,1
];
left join
load Group,median(Value) as Median resident Data group by Group;
output:
noconcatenate
load Date,Group,if(len(trim(Value))=0,Median,Value) as Value resident Data;
drop table Data;
output: