topic Re: Handle unmatched values in ApplyMap conditional statement in App Development

Handle unmatched values in ApplyMap conditional statement

FreeBI — Sat, 16 Nov 2024 19:26:22 GMT

Hi everyone,

In my model, I create a new column by using ApplyMap in a conditional If statement. I give a default value to the records which don't have a match but when it is a text value, it occupy a lot of space in my QVDs. I tried to put numerical codes for the unmatched values which makes it very hard for the business to recognize the meaning of the number of error.

Is there any best practice to handle these kind of issues?

Example:

if(isnull(Table1.Table1Key)
,-1
,if(isnull(Table2.Table2Key)
, -1
,Applymap('Mapping_Example',Table3.Table3Key,-1))) as Column1

Could anyone recommand a way to handle the unmatched values?

Thank you in advance!

Re: Handle unmatched values in ApplyMap conditional statement

Kushal_Chawda — Tue, 17 Dec 2019 14:52:29 GMT

load it default value as a number for eg. 1 within table. Then create a separate column with distinct values and link it with original value.

Below which you are already doing it

if(isnull(Table1.Table1Key)
,-1
,if(isnull(Table2.Table2Key)
, -1
,Applymap('Mapping_Example',Table3.Table3Key,-1))) as Column1

Now create a same column with distinct value and there you replace -1 with any value and link with Column1

Column1:

Column1,

if( Column1=-1,'Default',Column1) as Column2;

fieldvalue('Column1',recno()) as Column1

autogenerate fieldvaluecount('Column1');

Now use column2 as display

Re: Handle unmatched values in ApplyMap conditional statement

rwunderlich — Tue, 17 Dec 2019 15:31:31 GMT

I want to clarify your statement about text values using a lot of space in the QVD. Do you mean if you use a fixed value like "UNKNOWN"? That should take the same amount of space in a QVD as "-1".

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Handle unmatched values in ApplyMap conditional statement

FreeBI — Wed, 18 Dec 2019 09:59:12 GMT

I used to put fixed values as 'Unknow value' for a table with 100 millions records. My QVDs were around 5Go. When I replaced the text value by a numerical value, it went down to 3,5Go.

Re: Handle unmatched values in ApplyMap conditional statement

rwunderlich — Wed, 18 Dec 2019 14:17:43 GMT

That's odd. Did you use a fixed value for the unknown, or a unique string like "Unknown Value - " & field?

-Rob