Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
FreeBI
Contributor II
Contributor II

Handle unmatched values in ApplyMap conditional statement

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!

 

Labels (3)
4 Replies
Kushal_Chawda

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

FreeBI
Contributor II
Contributor II
Author

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.

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob