topic simplification of code in QlikView

simplification of code

deep2021 — Tue, 15 Jun 2021 11:22:26 GMT

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.

Re: simplification of code

sunny_talwar — Thu, 10 Jun 2021 13:35:12 GMT

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

Re: simplification of code

deep2021 — Thu, 10 Jun 2021 14:17:49 GMT

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

Re: simplification of code

sunny_talwar — Thu, 10 Jun 2021 15:30:47 GMT

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

Re: simplification of code

deep2021 — Tue, 15 Jun 2021 11:20:53 GMT

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.