Best Of
Re: Can you subtract days based on case when statements?
You will want to use the DATE_SUB function to subtract days from a given date. It would look like this:
Case when 'ID' = '1066' THEN DATE_SUB(`Maturity Date`, INTERVAL 45 day) when 'ID' = '1019' THEN DATE_SUB(`Maturity Date`, INTERVAL 55 day) when 'ID' = '2600' THEN DATE_SUB(`Maturity Date`, INTERVAL 66 day) when 'ID' = '2507' THEN DATE_SUB(`Maturity Date`, INTERVAL 70 day) when 'ID' = '1075' THEN DATE_SUB(`Maturity Date`, INTERVAL 75 day) when 'ID' = '2669' THEN DATE_SUB(`Maturity Date`, INTERVAL 85 day) ELSE DATE_SUB(`Maturity Date`, INTERVAL 66 day) END
As far as returning a floating decimal instead of a date, I'm not sure what you would expect to be returned when you are wanting to subtract a number of days from a date.
Re: Format Integer or Text String as Date
Your Calendly date field is being store in millisecond unix timestamp. You can convert it to seconds by dividing by 1000 and then using FROM_UNIXTIME to convert it to an actual datetime type
FROM_UNIXTIMESTAMP(`First Booked Calendly Date`/1000)
Re: How to create a column of data
Hi @DashboardDude , thank you for the help and the video :) and it should be "date"* on the title of my topic, sorry, i wrote it wrong.
Luiza
Re: How can I convert an Excel date code into a normal M/D/Y formatted date?
Hi @HowDoIDomo ,
So the start of that 4** date is Jan 1, 1900 so you need to create a column in an ETL with that Date and then add your number of days to that date. I made a video for you to see:
John Le
You're only one dashboard away.
More video solutions at: https://www.dashboarddudes.com/pantry
Re: Creating a DataBase on Magic ETL
@luizalauretti You would want Area to be stored as Text with a value of SP.
Re: Colour condition for words in a string
You could also utilize a regular expression to modify your string and automatically insert the HTML code.
Something like this might work assuming your values are actual colors (untested)
REGEXP_REPLACE(`RAG History`, '.*((Yellow)|(Red)|(Green)).*', '<div><span style="color: $1">$1</span></div>')
Re: Using RegEx to Extract Date
Try this:
REGEXP_REPLACE(`string`, '^.*(\d{2}-[A-z]{3}-\d{4}).*$', '$1')
You need to replace the entire string not just the substring. This is why there's ^.* and .*$ to match anything before and after the date value respectively.