Learn how to build a CASE expression in DS ETL FormulaWizard to map invoice status codes to different amounts. Status 1 pulls the transaction amount, status 2 pulls the ship amount, status 3 returns zero, and anything else returns null.
Transaction amount (open).
Ship amount (closed).
Zero (void).
Status Description.
April 9, 2014.
I'm going to build a new field or formula using a case statement on my invoice table and the invoice has a field which is status for open, closed or void transactions and depending on the amount of the status field I want to return. If it's one open, I want to return the transaction amount. So I'm going to double click transaction amount. If it's closed, I want to return let's say ship amount and if it's void, I want to bring zero for instance, so I'm going to bring constant numeric and I want to find zero. So these are the three fields I want to add to my case statement. So now I'm going to select the first field and I'm going to apply the case. So click the case button, select the filter. So if Status equals to 1, which is open, I click insert. So if status equals to one, it return transaction amount. I'm going to select the second one, click the case button and select the next filter which is status equals to 2. And finally I want to click the same thing for void insert. So if status is one returns transaction amount, if is two, returns ship amount, if it's three, returns zero. And if it's any other amount it will return null. So now I can save. I'm going to call status description, click ok and I just saved an expression using case statements.
