Data Transformation Missing Options

I will work on that. With a wiki article about a specific feature like this, it would be helpful if the article would simply state which build the feature is included in.

Sounds good, let me know if you are still not seeing it after update.

I'm seeing it now, but now I have a new problem: my data is not complete (i.e. not all my customers elected to tell us their birthday). How can I tell Yellowfin to calculate a date when the integer is valid and use null when it is not? Right now it looks like it is excluding all of my customers who didn't provide birthdays.

Hi Stephen,

It should just pass nulls through the inline function, is it actually removing rows from the result set?

Nathan

The software which this database was built for (i.e. we cannot change this behavior) stores non-dates as zero instead of null. I tried to implement transformation steps that would filter two copies of the dataset based on the date field, convert the valid dates to dates and convert the zeroes to null, then union the two sets. I couldn't figure out how to convert a zero numeric date to null. The field calculator step does not allow for freehand calculations, CASE statements, or null (as far as I can tell).

I've abandoned that approach and I'm just going to write freehand SQL...

Hi Stephen,

If you want to introduce nulls in case statements simply dont include anything for that case. Say I have 3 regions (AUS, USA, EMEA) and I want all entries with USA to be null, I would write:

CASE WHEN region!='USA' THEN my_value END

With this statement all non-usa regions will return my_value, and all USA regions will return null.

Nathan

Hi Stepen,

My apologies, I temporarily spaced out and forgot you were referring to the ETL calc field builder. Yes this does not handle cases, but we should be introducing something soon that will allow this.

Glad you have another work around.

Nathan

Hi Nathan,

I'm handling everything now using freehand SQL imports. Can you please lend some advice on handling time data? We've got measurements of time as a quantity not time as a time stamp (i.e. we performed 7.36 hours of service today). How can I handle this kind of data? Time stamps want to stick a date on the front. Should we just leave them as decimal hours rather than display it as HH:mm:ss?

Hi Stephen,

One idea is to multiply this field value by 3600 to get a seconds representation of the time difference which can then be converted in to HH:mm:ss at the report level using the HH:mm:ss column formatter.

Let me know if this works for you.

Nathan

We decided to avoid timestamps for durations of time. Instead we're leaving them as integers representing number of seconds (the finest grain detail we have).

Okay, sounds good. The option presented above is how I personally solved a similar issue on our internal reports so it might be worth exploring if you have not.

Nathan