Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
It's difficult to suggest how this might be simplified and/or optimized. Things which are noticeable are:
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
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.
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