topic Re: Please help me with Nested if in qlikview in QlikView

Please help me with Nested if in qlikview

Tue, 21 Jan 2014 21:30:16 GMT

If([ST_Date_Became_Client]<"2013-01-01 00:00:00","Qualified",

IIf([ST_Date_Became_Client]>="2013-01-01 00:00:00" And Inv_asset_Amt>=1000000,"Qualified",

IIf([ST_Date_Became_Client] Is Null And Inv_asset_Amt>=1000000,"Qualified",

IIf(([ST_Specialty_Unit] In ("Business Owner","Corporate Executive","Legal","Medical","Real Estate Investor","International","Sports and Entertainment") OR

[TEAM_NM] Like "PWM - S&E*" OR [TEAM_NM] Like "IWM*" OR [TEAM_NM] Like "No Team - Sports*"),"Qualified",

IIf([CUSTOMER_NAME]="INV REVENUE < $10","Qualified",

IIf([ITEM_ID] In ("CM_CRP_FDP","CM_FXD_INC","CP_LEASE_1","EBS_IISDLY","ES_1031EX","ES_DEPACCT",

"ES_ESCROW","FDR_BCARD1","FOREIGN_EX","LTR_OF_CRD","M_BUS_401K","MERCH_SVCS","T_CD_CORP"),"Qualified","Non-Qualified")))))) AS QUALIFICATION,

Thank you very much.

Re: Please help me with Nested if in qlikview

iktrayanov — Tue, 21 Jan 2014 21:42:44 GMT

Some description of the problem would be helpful.

Re: Please help me with Nested if in qlikview

iktrayanov — Tue, 21 Jan 2014 21:54:08 GMT

Try This

LOAD

If([ST_Date_Became_Client]<'2013-01-01 00:00:00','Qualified',

If([ST_Date_Became_Client]>='2013-01-01 00:00:00' And Inv_asset_Amt>=1000000,'Qualified',

If(IsNull([ST_Date_Became_Client]) = -1 And Inv_asset_Amt>=1000000,'Qualified',

If((match([ST_Specialty_Unit],'Business Owner','Corporate Executive','Legal','Medical','Real Estate Investor','International','Sports and Entertainment')<> 0 OR

wildmatch([TEAM_NM], 'PWM - S&E*' ,'IWM*' , 'No Team - Sports*'),'Qualified',

If([CUSTOMER_NAME]='INV REVENUE < $10','Qualified',

If(math([ITEM_ID],'CM_CRP_FDP','CM_FXD_INC','CP_LEASE_1','EBS_IISDLY','ES_1031EX','ES_DEPACCT',

'ES_ESCROW','FDR_BCARD1','FOREIGN_EX','LTR_OF_CRD','M_BUS_401K','MERCH_SVCS','T_CD_CORP') <> 0,'Qualified','Non-Qualified')))))) AS QUALIFICATION,

Re: Please help me with Nested if in qlikview

maxgro — Tue, 21 Jan 2014 21:54:33 GMT

... and a .qvw with some data, if possible

if not you could start here

If( [ST_Date_Became_Client]<'2013-01-01 00:00:00', 'Qualified',

If( [ST_Date_Became_Client]>='2013-01-01 00:00:00' And Inv_asset_Amt>=1000000, 'Qualified',

If( isnull([ST_Date_Became_Client]) And Inv_asset_Amt>=1000000, 'Qualified',

If( match([ST_Specialty_Unit], 'Business Owner', 'Corporate Executive', 'Legal', 'Medical', 'Real Estate Investor',

'International','Sports and Entertainment') OR wildmatch(TEAM_NM, 'PWM - S&E*', 'IWM*', 'No Team - Sports*'), 'Qualified',

If( CUSTOMER_NAME='INV REVENUE < $10', 'Qualified',

If( match(ITEM_ID, 'CM_CRP_FDP','CM_FXD_INC','CP_LEASE_1','EBS_IISDLY','ES_1031EX','ES_DEPACCT',

'ES_ESCROW','FDR_BCARD1','FOREIGN_EX','LTR_OF_CRD','M_BUS_401K','MERCH_SVCS','T_CD_CORP'), 'Qualified',

'Non-Qualified'

)))))) AS QUALIFICATION,

perhaps

[ST_Date_Became_Client]<date#('2013-01-01 00:00:00', 'YYYY-MM-DD hh:mm:ss')

Re: Please help me with Nested if in qlikview

Tue, 21 Jan 2014 22:05:46 GMT

Match function is IN type function. So please use Match or WildMatch function to complete your expression.

Re: Please help me with Nested if in qlikview

stevedark — Tue, 21 Jan 2014 23:09:02 GMT

I would advise that you break the statement down into bite sized chunks.

Create a number of fields, each with part of your IF statement. You can then see each of those fields working independently, and fix any part that is not working. Once that is done you can put your statement back together again, into a single field - giving the result you want.

Obviously care needs to be taken with brackets, and I suspect the issue (as other contributors have noted) is that you need to use a Match or Wildmatch rather than the IN statement.

Steve

Re: Please help me with Nested if in qlikview

Wed, 22 Jan 2014 00:37:28 GMT

Try the below

if( [ST_Date_Became_Client] < '2013-01-01 00:00:00'
OR([ST_Date_Became_Client]>='2013-01-01 00:00:00' AND Inv_asset_Amt>='1000000')
OR(isnull([ST_Date_Became_Client]) And Inv_asset_Amt>=1000000)
,'Qualified','Non-Qualified') as 'Qualification',