Simplifying if conditions in the script

[deep2021]

Hi All,

PFB script.

Can you please suggest how to simplify if statement from the script.

if(ApplyMap('AcquisitionMonth',ASSET_STATIC_ID,null())=Month, ApplyMap('AcquisitionMonth',ASSET_STATIC_ID,null()),null()) as ASSET_ACQUISITION_MONTH,
if(ApplyMap('AcquisitionQuarter',ASSET_STATIC_ID,null())=Quarter, ApplyMap('AcquisitionQuarter',ASSET_STATIC_ID,null()),null()) as ASSET_ACQUISITION_QUARTER,

if( len(Month)>2 and [Object Type Code]='A',(
ApplyMap('DispositionMonth',[%KEY_ASSET_SCD],'-')),
if(len(Quarter)>2 and [Object Type Code]='A',(
ApplyMap('DispositionQuarter',[%KEY_ASSET_SCD],'-')))) as PropertySold_Diversification,


if(len(Month)>2 and [Object Type Code]='A',(
ApplyMap('DispositionMonth_asset',[%KEY_ASSET_SCD],'-')),
if(len(Quarter)>2 and [Object Type Code]='A',(
ApplyMap('DispositionQuarter_asset',[%KEY_ASSET_SCD],'-')))) as PropertySold_Asset_download,


if( len(Quarter)>2,(
if(((Applymap('On_Off_Rules_Start_Qtr_MVRF',VEHICLE_STATIC_ID,'')<= Replace(Quarter,'Q','')) and
(Replace(Quarter,'Q','')<= Applymap('On_Off_Rules_End_Qtr_MVRF',VEHICLE_STATIC_ID))),'PL_OFF','0')
),
if( len(Month)>2,(
if(((Applymap('On_Off_Rules_Start_Mth_MVRF',VEHICLE_STATIC_ID,'')<= (num#(Month))) and
((num#(Month))<= Applymap('On_Off_Rules_End_Mth_MVRF',VEHICLE_STATIC_ID))),'PL_OFF','0')))) as PL_OFF,


if( len(Quarter)>2,(
if((((Applymap('On_Off_Rules_Start_Qtr_NAV',VEHICLE_STATIC_ID,'')<= Replace(Quarter,'Q','')) and
(Replace(Quarter,'Q','')<= Applymap('On_Off_Rules_End_Qtr_NAV',VEHICLE_STATIC_ID)))) ,'IL_OFF','0')
),
if( len(Month)>2,(
if((((Applymap('On_Off_Rules_Start_Mth_NAV',VEHICLE_STATIC_ID,'')<= (num#(Month))) and
((num#(Month))<= Applymap('On_Off_Rules_End_Mth_NAV',VEHICLE_STATIC_ID)))),'IL_OFF','0')))) as IL_OFF,


Thanks

[marcus_sommer]

It's difficult to suggest how this might be simplified and/or optimized. Things which are noticeable are:

• the applymap() are executed twice within the if-loop - this may not mandatory necessary else the default-value from applymap() may be enough
• like recommended in the previous posting the mapping might be done within the first load and checks and any deducing from the results may be applied within a preceeding load of it
• there are replace() and converting measures within the if-loops which may not necessary at all and/or be resolved within working-steps in beforehand
• multiple different calendar-fields may be associated with own calendars and/or canonical calendars instead of embedding them

Beside this the script-snippets look like as if a lot of fields are - rather complex - created. It reminds me a bit of approaches which would be needed within a table-calculation. Within databases and/or BI tools like Qlik is it the aim to work rather on table-levels to transform and merge the needed information as to extend the logics from a table-calculation. Although this is in general possible it's not optimized in regard to the performance and also not to the efforts to develop and maintain such logics. Therefore here - changing the general approach how to develop the datamodels - may more potential as within the suggestions above.

- Marcus  

[deep2021]

Hi Markus,

Thanks for your valuable inputs. These calculation belongs to same table and there are number of if conditions. In that case if we can use where clause for the if condition does that makes any sense.

Thanks.

[marcus_sommer]

If the dataset should be reduced in any way it should be done as early as possible because the following loadings and transformation would be performed against as smaller dataset.

Like above hinted not every if-loop might be necessary if they are applied on top of each other and maybe als within multipler more particular loadings which are afterwards merged again.

- Marcus