simplification of code

[deep2021]

Hello Experts,

I want to simplify the below code in the script,

if([Object Type Code]='IAV' and LEN(Month)=0, [VEHICLE_STATIC_ID]&'|'&[INVESTOR_ACCOUNT_STATIC_ID]&'|'&Quarter,
if([Object Type Code]='IAV' and LEN(Quarter)=0, [VEHICLE_STATIC_ID]&'|'&[INVESTOR_ACCOUNT_STATIC_ID]&'|'&Month,
if([Object Type Code]='IA' and LEN(Month)=0, INVESTOR_ACCOUNT_STATIC_ID&'|'& Quarter,
if([Object Type Code]='IA' and LEN(Quarter)=0, INVESTOR_ACCOUNT_STATIC_ID&'|'& Month,
if([Object Type Code]='A' and LEN(Month)=0, ASSET_STATIC_ID&'|'& Quarter,
if([Object Type Code]='A' and LEN(Quarter)=0, ASSET_STATIC_ID&'|'& Month,
if([Object Type Code]='V' and LEN(Month)=0, VEHICLE_STATIC_ID&'|'& Quarter,
if([Object Type Code]='V' and LEN(Quarter)=0, VEHICLE_STATIC_ID&'|'& Month,
if([Object Type Code]='C' and LEN(Month)=0, COMPOSITE_ID&'|'& Quarter,
if([Object Type Code]='C' and LEN(Quarter)=0, COMPOSITE_ID&'|'& Month)))))))))) AS KEY_ID,




if([PERIOD]=ApplyMap('MAP_INVESTOR_VEHICLE_Month',%KEY_INVVEH_ID, '-'),'New Investor',
if([PERIOD]=ApplyMap('MAP_INVESTOR_VEHICLE_Quarter',%KEY_INVVEH_ID, '-'),'New Investor',
if([PERIOD]=ApplyMap('MAP_INVESTOR_VEHICLE_END_Month',%KEY_INVVEH_ID, '-'),'Exiting Investor',
if([PERIOD]=ApplyMap('MAP_INVESTOR_VEHICLE_END_Quarter',%KEY_INVVEH_ID, '-'),'Exiting Investor'
)))) as [Investor New/Exit],

could you please suggest the possible ways for the same.

Thanks.

[sunny_talwar]

What are you trying to recreate? the whole expression or just creating KEY_ID? If KEY_ID is needed, you can try this

Pick(Match([Object Type Code], 'IAV', 'IA', 'A', 'V', 'C'),
[VEHICLE_STATIC_ID]&'|'&[INVESTOR_ACCOUNT_STATIC_ID],
INVESTOR_ACCOUNT_STATIC_ID,
ASSET_STATIC_ID,
VEHICLE_STATIC_ID,
COMPOSITE_ID)
&'|'&
If(LEN(Month)=0, Quarter, Month) as KEY_ID

[deep2021]

Hi Sunny,

Thanks for the valuable response.

Can you please suggest the possible way for the below part to avoid the repetitive calculation.

if([PERIOD]=ApplyMap('MAP_INVESTOR_VEHICLE_Month',%KEY_INVVEH_ID, '-'),'New Investor',
if([PERIOD]=ApplyMap('MAP_INVESTOR_VEHICLE_Quarter',%KEY_INVVEH_ID, '-'),'New Investor',
if([PERIOD]=ApplyMap('MAP_INVESTOR_VEHICLE_END_Month',%KEY_INVVEH_ID, '-'),'Exiting Investor',
if([PERIOD]=ApplyMap('MAP_INVESTOR_VEHICLE_END_Quarter',%KEY_INVVEH_ID, '-'),'Exiting Investor'
)))) as [Investor New/Exit],



if(WildMatch(PLSR_Footnote_temp,'*D*') OR (INDEX(PLSR_Footnote_temp,'*')>1),'ZERO','NOT ZERO') as STATUS,
if(WildMatch(PLSR_Footnote_temp,'*D*') OR (INDEX(PLSR_Footnote_temp,'*')>1),0,BEG_BAL) as BEG_BAL_1,
if(WildMatch(PLSR_Footnote_temp,'*D*') OR (INDEX(PLSR_Footnote_temp,'*')>1),0,END_BAL) as END_BAL_1,
if(WildMatch(PLSR_Footnote_temp,'*D*') OR (INDEX(PLSR_Footnote_temp,'*')>1),0,ACTIVITY) as ACTIVITY_1

[sunny_talwar · Accepted solution]

For the second one, you can try this

If(Match([PERIOD], ApplyMap('MAP_INVESTOR_VEHICLE_Month',%KEY_INVVEH_ID, '-'), ApplyMap('MAP_INVESTOR_VEHICLE_Quarter',%KEY_INVVEH_ID, '-')), 'New Investor',
'Exiting Investor') as [Investor New/Exit],

Not sure if the next 4 fields can be simplified

[deep2021]

Thanks Sunny for your valuable inputs.

Please have a look on the below part.

Table1:

load

if( len(Month)>2 and [Object Type Code]='A',(

if( ((LEFT((num#(Month)),4)='2010') AND (ApplyMap('PLSR_HISTORIC',VEHICLE_STATIC_ID)='REIT')),'CAL1' ,
IF( (LEFT((num#(Month)),4)<='2009'), 'CAL1'))),

if(len(Quarter)>2 and [Object Type Code]='A',(

if( ((NUM#(LEFT(Quarter,4))='2010') AND (ApplyMap('PLSR_HISTORIC',VEHICLE_STATIC_ID)='REIT')),'CAL1',
if( (NUM#(LEFT(Quarter,4))<='2009'),'CAL1'))))) as PLSR_CAL_HISTORIC,


if([Object Type Code] ='A' AND ((LEFT((num#(Month)),4)<='2010') OR (NUM#(LEFT(Quarter,4))<='2010')) and
// (ASSET_STATIC_ID='10649' or ASSET_STATIC_ID='10650' or ASSET_STATIC_ID='10651' or ASSET_STATIC_ID='10660'),'RTN',
wildmatch(ASSET_STATIC_ID,'10649','10650','10651','10660'),'RTN',


if( len(Month)>2 and [Object Type Code]='A',(

if( ((LEFT((num#(Month)),4)='2010') AND (ApplyMap('PLSR_HISTORIC',VEHICLE_STATIC_ID)='REIT')),'CAL1',
IF( (LEFT((num#(Month)),4)<='2009'), 'CAL1'))),

if(len(Quarter)>2 and [Object Type Code]='A',(

if( ((NUM#(LEFT(Quarter,4))='2010') AND (ApplyMap('PLSR_HISTORIC',VEHICLE_STATIC_ID)='REIT')),'CAL1',
if( (NUM#(LEFT(Quarter,4))<='2009'),'CAL1')))))) as ILSR_CAL_HISTORIC,

if([Object Type Code] ='A' AND ((LEFT((num#(Month)),4)<='2010') OR (NUM#(LEFT(Quarter,4))<='2010')) and


wildmatch(ASSET_STATIC_ID,'10649','10650','10651','10660'),'TOTAL FOR RTN',

//(ASSET_STATIC_ID='10649' or ASSET_STATIC_ID='10650' or ASSET_STATIC_ID='10651' or ASSET_STATIC_ID='10660'),'TOTAL FOR RTN',

if( len(Month)>2 and [Object Type Code]='A',(

if( ((LEFT((num#(Month)),4)='2010') AND (ApplyMap('PLSR_HISTORIC',VEHICLE_STATIC_ID)='REIT')),'TOTAL FOR NON-RTN',
IF( (LEFT((num#(Month)),4)<='2009'), 'TOTAL FOR NON-RTN'))),

if(len(Quarter)>2 and [Object Type Code]='A',(

if( ((NUM#(LEFT(Quarter,4))='2010') AND (ApplyMap('PLSR_HISTORIC',VEHICLE_STATIC_ID)='REIT')),'TOTAL FOR NON-RTN',
if( (NUM#(LEFT(Quarter,4))<='2009'),'TOTAL FOR NON-RTN')))))) as ILSR_CAL_HISTORIC2

from t1;

Here I am creating flags but can you please assist, how to use pick match when there are multiple conditions.