topic Re: Imputation for missing strings in QlikView in QlikView

Imputation for missing strings in QlikView

Nihat — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Imputation for missing strings in QlikView

Kushal_Chawda — Tue, 17 Dec 2019 14:37:56 GMT

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;

Re: Imputation for missing strings in QlikView

Taoufiq_Zarra — Tue, 17 Dec 2019 21:02:29 GMT

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 :

Re: Imputation for missing strings in QlikView

Brett_Bleess — Mon, 23 Dec 2019 18:47:50 GMT

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

Re: Imputation for missing strings in QlikView

Nihat — Thu, 02 Jan 2020 13:12:44 GMT

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!

Re: Imputation for missing strings in QlikView

Kushal_Chawda — Thu, 02 Jan 2020 13:42:51 GMT

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;