Imputation for missing strings in QlikView

[Nihat]

I have a big dataset with 20% of missing strings.

NAME      |  AREA
--------------------------
Andy      |  Sales
Andy      |  NA
Andy      |  Sales
Andy      |  Sales
Andy      |  NA
Andy      |  Sales
Sandy     |  Construction
Sandy     |  Construction
Sandy     |  NA
Sandy     |  Construction
Sandy     |  Construction
Wendy     |  Planting
Wendy     |  Driving
Wendy     |  NA
Wendy     |  NA
Wendy     |  NA

In most cases of my data it is almost obvious, that Andy does Sales and Sandy is in Construction. But we cannot make sure about Wendy.

My desirable result is:

NAME      |  AREA
--------------------------
Andy      |  Sales
Andy      |  Sales
Andy      |  Sales
Andy      |  Sales
Andy      |  Sales
Andy      |  Sales
Sandy     |  Construction
Sandy     |  Construction
Sandy     |  Construction
Sandy     |  Construction
Sandy     |  Construction
Wendy     |  Planting
Wendy     |  Driving
Wendy     |  NA
Wendy     |  NA
Wendy     |  NA

Which is the best way to handle it in QlikView?

Thanks in advance!

[Kushal_Chawda]

Data:
load * Inline [
NAME | AREA
Andy | Sales
Andy | NA
Andy | Sales
Andy | Sales
Andy | NA
Andy | Sales
Sandy | Construction
Sandy | Construction
Sandy | NA
Sandy | Construction
Sandy | Construction
Wendy | Planting
Wendy | Driving
Wendy | NA
Wendy | NA
Wendy | NA ] (delimiter is '|');

Left Join
Load Distinct NAME,
count( DISTINCT AREA) as AREA_DISTINCT_COUNT
Resident Data
where trim(AREA)<>'NA'
Group by NAME;

Left Join
Load Distinct NAME,
AREA as NEW_AREA
Resident Data
where AREA_DISTINCT_COUNT=1 and trim(AREA)<>'NA';

Final:
NoConcatenate
Load NAME,if(len(trim(NEW_AREA))=0,AREA,NEW_AREA) as AREA
Resident Data;

Drop Table Data;

[Taoufiq_Zarra · Accepted solution]

Hi @Kushal_Chawda 

My proposal :

TEMPS:
load Rowno() as ID,* inline [
NAME,AREA
Andy,Sales
Andy,NA
Andy,Sales
Andy,Sales
Andy,NA
Andy,Sales
Sandy,Construction
Sandy,Construction
Sandy,NA
Sandy,Construction
Sandy,Construction
Wendy,Planting
Wendy,Driving
Wendy,NA
Wendy,NA
Wendy,NA
];

map1:
mapping load
NAME as CNAME,
mode(if(AREA<>'NA',AREA)) as CAREA
resident TEMPS
GROUP BY NAME;


RESULT:
load
ID as New_ID,
NAME as New_NAME,
if(isnull(ApplyMap('map1', NAME)),AREA,ApplyMap('map1', NAME)) As New_AREA
resident TEMPS;

DROP TABLE TEMPS;

output :

[Brett_Bleess]

IF either of the posters solutions worked for you, be sure to return to the thread and use the Accept as Solution button on the post(s) that helped to give them credit for the assistance and let other Community Members know what worked for you.  

The only additional thing I have is base URL link to the Design Blog area where there are hundreds of how-to posts on a variety of topics that may give you some additional ideas:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[Nihat]

Thank you for the reply,

It seems working for me when I use one map, but I have some problems in case of multiple mappings:

map1:
mapping load
Name as CName,
mode(if(Occupation1<>'NA',Occupation1)) as COccupation1
resident Temp_CAD_DATA;

map2:
mapping load
Name as CName,
mode(if(Occupation2<>'NA',Occupation2)) as COccupation2
resident Temp_CAD_DATA;

map3:
mapping load
Name as CName,
mode(if(Occupation3<>'NA',Occupation3)) as COccupation3
resident Temp_CAD_DATA;

GROUP BY Name;

RESULT:
load
if(isnull(ApplyMap('map1', Name)),Occupation1,ApplyMap('map1', Name)) As Occupation1
resident Temp_CAD_DATA;

load
if(isnull(ApplyMap('map2', Name)),Occupation2,ApplyMap('map2', Name)) As Occupation2
resident Temp_CAD_DATA;

load
if(isnull(ApplyMap('map3', Name)),Occupation3,ApplyMap('map3', Name)) As Occupation3
resident Temp_CAD_DATA;

DROP TABLE Temp_CAD_DATA;

Thanks in advance!

[Kushal_Chawda · Accepted solution]

You need to apply Group by in individual mapping table. Also, combine if ..else in single load instead of doing it in separate load

map1:
mapping load
Name as CName,
mode(if(Occupation1<>'NA',Occupation1)) as COccupation1
resident Temp_CAD_DATA
GROUP BY Name;

map2:
mapping load
Name as CName,
mode(if(Occupation2<>'NA',Occupation2)) as COccupation2
resident Temp_CAD_DATA
GROUP BY Name;

map3:
mapping load
Name as CName,
mode(if(Occupation3<>'NA',Occupation3)) as COccupation3
resident Temp_CAD_DATA;
GROUP BY Name;


RESULT:
load
if(isnull(ApplyMap('map1', Name)),Occupation1,ApplyMap('map1', Name)) As Occupation1,
if(isnull(ApplyMap('map2', Name)),Occupation2,ApplyMap('map2', Name)) As Occupation2,
if(isnull(ApplyMap('map3', Name)),Occupation3,ApplyMap('map3', Name)) As Occupation3
resident Temp_CAD_DATA;


DROP TABLE Temp_CAD_DATA;