Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
ihuynhi
Contributor II
Contributor II

Fill empty values with median of the group

Hi!

I'm new to Qlik so apologies if I use the wrong terms. I have the following table with missing values

snip.PNG

and I would like to fill in the empty values with the group median so I would get this

snip2.PNG

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?

 

Labels (3)
1 Solution

Accepted Solutions
Taoufiq_Zarra

@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:

Taoufiq_Zarra_0-1652429446833.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

1 Reply
Taoufiq_Zarra

@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:

Taoufiq_Zarra_0-1652429446833.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉