Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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.
That's odd. Did you use a fixed value for the unknown, or a unique string like "Unknown Value - " & field?
-Rob