Please help me with Nested if in qlikview

[#1]

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.

[iktrayanov]

Some description of the problem would be helpful.

[iktrayanov · Accepted solution]

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,

[maxgro]

... 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')

[#5]

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

[stevedark]

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

[#7]

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',