multiple if statement to generate new column

[QlikBeginner1]

IF(ARCSourceSystemNamePolicy = 'IPS' AND IsNull(ARCPrimaryTradeDesc) OR ARCPrimaryTradeDesc = '-',IPSTrade,ARCPrimaryTradeDesc),
IF(ARCSourceSystemNamePolicy = 'IMACS' AND IsNull(ARCPrimaryTradeDesc) OR ARCPrimaryTradeDesc = '-',IMACSTRADE, ARCPrimaryTradeDesc) as Trade

Hi,

These two expressions work seperately when I add them into the front end, I am trying to use both if statements to generate the Trade column, however this is giving me null values, how should the syntax be?

[Or]

What formula are you using to try and generate the Trade column? Obviously, you can't use two separate formulas to generate one column, so perhaps you've reworked it into one formula?

[QlikBeginner1]

If(Match(ARCSourceSystemNamePolicy, 'IPS' AND IsNull(ARCPrimaryTradeDesc)), IPSTrade,
IF(MATCH(ARCSourceSystemNamePolicy,'IMACS' AND IsNull(ARCPrimaryTradeDesc)),IMACSTRADE,ARCPrimaryTradeDesc)) as Trade

I have also tried this but still showing NULL values 

[QlikBeginner1]

[Or]

Perhaps:

IF(ARCSourceSystemNamePolicy = 'IPS' AND IsNull(ARCPrimaryTradeDesc) OR ARCPrimaryTradeDesc = '-',IPSTrade,
IF(ARCSourceSystemNamePolicy = 'IMACS' AND IsNull(ARCPrimaryTradeDesc) OR ARCPrimaryTradeDesc = '-',IMACSTRADE, ARCPrimaryTradeDesc)) as Trade

[QlikBeginner1]

Still returns NULL values! 

Its a bit messy as I am using excel files to create a Trade field before I then try this IF statement,  IMACSTRADE and IPSTRADE have been generated by if statement to compare two columns , one from a excel file and one from a SQL table, and return the column which does not have a NULL value. I then am concatinating a few tables as there are different sources into one main table, and this allows to merge the two source trades together before they are concatinated into one final table, i hope that makes sense. 

I have all others working but just the syntax for these two if statements, else to return ARCPrimaryTradeDesc, I have tried a few methods but doesnt seem to return any values, the screenshot I pasted shows the values that should appear on the left of the trade column which has a NULL value

[Or]

In the screen capture, the underlying data used to determine the Trade isn't included, so unfortunately that doesn't help in trying to write a formula...

One thing I would probably do real quick is parenthesize the conditions, just to make sure they're being read in the right order. I think:

IF(ARCSourceSystemNamePolicy = 'IPS' AND (IsNull(ARCPrimaryTradeDesc) OR ARCPrimaryTradeDesc = '-'),IPSTrade,
IF(ARCSourceSystemNamePolicy = 'IMACS' AND (IsNull(ARCPrimaryTradeDesc) OR ARCPrimaryTradeDesc = '-'),IMACSTRADE, ARCPrimaryTradeDesc)) as Trade

[QlikBeginner1]

I now have it working in the front end, however when I try to use that same expression in the back end and name it as 'Trade', it gives null values, however both expressions are the same?

If(Match(ARCSourceSystemNamePolicy, 'IPS') AND IsNull(ARCPrimaryTradeDesc) OR ARCPrimaryTradeDesc = '-', IPSTrade,
IF(MATCH(ARCSourceSystemNamePolicy,'IMACS') AND IsNull(ARCPrimaryTradeDesc) OR ARCPrimaryTradeDesc = '-',IMACSTRADE,ARCPrimaryTradeDesc)) as Trade

[Or]

Would you be able to provide a small set of sample data (in Excel / inline format so it can be reloaded locally)?

[QlikBeginner1 · Accepted solution]

@Or  Thank you so much for assisting, silly enough the reason why it was missing when in the load editor was because I was LEFT joining, when I just joined the trades was populating. Thank you for your time and effort to helping me resolve the issue 🙂