Hi All
I am trying to create a custom field in the Data Stage where I need to test the contents of a field and depending on the result return either Field A or B.
for example:
if [Field A] = Null then [Field B] ELSE [Field A]
how will this look in the Custom field?
Thanks for your help
Malcolm
Hi Malcolm,
You can create a custom field with 2 fixed value, setup conditions for each fixed value.
For example, i have a Customer No. field that will return Bill-to Customer No. if it is not empty else return Sell-to Customer No.
Hope this helps.
Regards,
JK
Malcom! I know i'm late to the game but ... The fastest way of writing this would be with a CASE statement (as a Custom Field Transformation)
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql
It can still be Parameterized if you drag in the field names into the dialog box of the Custom Transformation Editor.
Case
WHEN [Bill-To Customer No.] = '' then [Sell-to Customer No.]
ELSE [Sell-To Customer No.]
END
I prefer Case Statements especially in situations where there are many conditions to consider or there are multiple conditions.
Jae,
Good advice.
I need to take this one step further. Can you reference another table in the Case statement? I can't find any examples.
I have a setup table (called AgingFmt) that has about 10 records. Each row has 5 aging category titles and days. i.e. 30 Days, 30, 60 days,60, 90 days, 90 etc. The rows contain a company code and so does my invoice header so I need to join on that.
I need to populate a row in my inventory header table with the word '30 Days', or '60 Days' etc. based on the age of the invoice.
I've gotten it to work via a custom Case statement by HARD CODING the day ranges (i.e. 30, 60 etc). But I can't figure out how to reference the number of days from the AgingFmt table because the Custom Tranformation editor only shows the fields from the current table. Is there a way to reference related fields from another table?
You'll need to look at creating a lookup from AgingFmt to your inventory header table. The Jet Data Manager User Guide will show you how to establish a relation between the two tables and then drag and drop the needed field. Once it's in the header you can reference it like any other table.
@David's advice is actually (possibly) backwards.
Instead of adding Aging as an Attribute of the Inventory Header, it may make more sense to make Aging an attribute of the Fact.
In other words.
Treat aging as a Dimension where the key level is NumberOfDaysOld, and attributes for rollup (eg. 30,60,90, or 60,90,120). The granularity of the AgingDimension would then be one row for each number of days old (1,2,3,4,5,6,7 days ... etc.)
Then on the fact table calculate NumberOfDaysOld.
By implementing Aging as a Dimension, you can accommodate multiple roll up models as well as recycle the table for AR and AP aging.
----
If you rather keep the route David's describing, you need to add the field as a Conditional Lookup:
https://jetsupport.jetreports.com/hc/en-us/articles/360004071334-Adding-a-Conditional-Lookup-Field
Thanks for the response to both of you.
Yes I know how to use a conditional lookup and yes, I believe David has it reversed.
The way the NAV example DW works is the way you describe with a row for each aging 'bucket' (i.e. 30-60, 60-90) etc. It's seems to be a hard coded table in JDM and thus an end user can change those rows in JDM for a different set of buckets.
Epicor has a screen in the system for these aging buckets (and even allows more than one per company where the user can use a different set of buckets before running the AR or AP Aging report).
I'm ok with my DW just using the 'default' flagged set, but without completely tranforming the data from horizontal to vertical, i don't think your idea works.
I simply need either a conditional lookup or custom field that will find the right row based on Company ID and a hard coded value of ARSYST (which represents the default aging buckets for AR) and then use the right column in that row depending on how 'old' the invoice is. I've already got a custom field on the AR Invoice header calulating the age of the invoice.
The problem is both the custom field and the conditional lookup's allow you to drag out fields from the current table you are working on. It does NOT let you browse fields from related tables. I'm guessing you can access them via hand typing the SQL in, but I can't find any examples anywhere of the syntax in terms of how to reference a table and field and join based on the above paragraph.
In concept something like 'select agingbucket5 from AgingFMT(my aging table name) where companyid = companyid and formatcode='arsyst' and daysold (age of invoice) < agingdays5'
I need to do this 5 times for it to find the right row where you go down row by row until it isn't true (meaning the invoice age is < the bucket range.)
I was able to get this to work when I did a hardcode as follows. What I want is instead of '30,60,90' being in my formula, I want to reference the appropriate number from the appropriate column in my AgingFMT table.
Case
when [DaysOld] <30 then 0
when [DaysOld] <60 then 1
when [DaysOld] <90 then 2
when [DaysOld] <120 then 3
else 4
end
Here is what the Aging FMT Table looks like:
I know if I had to I could use a conditional lookup to copy most of the fields from my AgingRPTFmt table into my invoice header table and thus reference the approprate field but I feel that would be very sloppy and result in alot of data storage redundancy. I thinking there has to be an easier way.
