Alternate to If Statement

[#1]

Hi folks,

I have been sitting on this problem for a few days but have not been able to figure the best way to go about it.

Let me try to explain it:

Let's say there are 2 tables

Contracts

Contracts	Suppliers
[Contract Number]	Supplier
[Awarded Supplier]	Supplier_Parent
[Awarded Supplier Parent ID]	MS_vendor_tp_id AS Supplier_Parent_ID

I need to have an If statement to determine whether the member is using the Awarded Supplier by

Final Awarded Supplier: =if([Awarded Supplier Parent]=[MS_vendor_tp_id] OR [Awarded Supplier]=[Supplier_Parent], [Supplier_Parent])

I can later on refer back to this column to calculate the amount that was spent on awarded supplier using if statement, so on and so forth such as

=if(Not IsNull([Final Awarded Supplier]) OR (IsNull([Final Awarded Supplier]) AND (XRef?)='Y'),'Y','N')

So, the problem is I constantly need to use this column but when I create the If statement as an expression or calculated field, it does not give me the option to refer back to this column which result in tons of nested if, and Out of Object Memory message... I've been researching and I think the best way is to somehow convert this if statement to Pick(Match) in Load Resident statement but has been stuck as I am still a newbie.

My database is a star schema and is attached in case it helps with the question.

Thank you so much for your help in advance!

[Anonymous]

I imagine this is pretty late, but it's good to have this for a reference.  I run into it all the time.

I'm not quite sure how you're getting Awarded Supplier Parent above, so I'll just explain how I'd generally solve this problem.

Basically, you need to get all of these fields into the same table.  Then re-load the table into a new table and perform the IF statement after all of the fields are together in one table.

Now, I know you're thinking  'well, I don't want to put all of those fields into one table, it doesn't make sense dimensionally'.  And you're right.

But, you have to do it temporarily.  You can add these fields as temporary fileds:

1) Choose one table as the master table where you want to store the result

2) Left Join the other 'missing' fields into this table and name them something temporary

(2.1) make sure you don't expand the # of records of the master table which you picked on #1... I generally get a row count before and after the left join and compare it.. if the # of records are the same, then you're good

3) Rename the master table to a temporary name

4) NoConcatenate Resident load from the master temporary table into a new master table and perform your IF logic

5) Drop Field on all of the temporary field names, and drop the temporary master table

Added bonus, type in the code by hand with no syntax errors = take the rest of the day off.