topic Re: Simplifying if conditions in the script in QlikView

Simplifying if conditions in the script

deep2021 — Fri, 11 Jun 2021 17:05:26 GMT

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

Re: Simplifying if conditions in the script

marcus_sommer — Mon, 14 Jun 2021 09:08:07 GMT

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

Re: Simplifying if conditions in the script

deep2021 — Mon, 14 Jun 2021 15:03:14 GMT

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.

Re: Simplifying if conditions in the script

marcus_sommer — Tue, 15 Jun 2021 08:28:55 GMT

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