Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
deep2021
Creator III
Creator III

simplification of code

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.

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

4 Replies
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
Creator III
Creator III
Author

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

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
Creator III
Creator III
Author

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.