Data Transformation Missing Options

Stephen Johnson shared this problem 8 months ago
Resolved

According to the Yellowfin Wiki, I should be able to put in a transformation step to convert numeric data to SQL Dates: http://wiki.yellowfin.bi/display/USER74/Numeric+to+SQL+Date

This doesn't work for me. When I try to open the conversion menu I am not offered the same options described in the wiki article:

/2fKEfnRwJlgAAAABJRU5ErkJggg==

I am using 7.4 build 20180313. I don't see anything on the wiki page that describes what build the feature is contained in. What am I missing here?

Comments (12)

photo
1

Hi Stephen,

This option is present in my 7.4 build which means that this has probably been added within the last couple months. Are you able to upgrade to the latest 7.4 build (20180505)?

Nathan

photo
1

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.

photo
1

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

photo
1

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.

photo
1

Hi Stephen,

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

Nathan

photo
1

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...

photo
1

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

photo
1

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

photo
1

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?

photo
1

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

photo
1

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).

photo
1

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

photo