Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
Some description of the problem would be helpful.
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,
... 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')
Match function is IN type function. So please use Match or WildMatch function to complete your expression.
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
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',