Another question:
Can I import my own SQL function...

# gooddata-platformp

Philippe Hébert

02/02/2022, 8:50 PMAnother question:
Can I import my own SQL functions to GoodData hosted? If not, what alternatives do I have?
Possible examples:
• Age of a person (so DATE_DIFF in year, with 0.00 level of precision)
• Union or intersection of intervals of time (example lib that does that in JS: https://moment.github.io/luxon/api-docs/index.html#interval)

m

Michal Hauzírek

02/02/2022, 9:37 PMYou currently can not import our own SQL functions into GoodData MAQL. So you would either need to pre-calcultate the calculations before loading it to GoodData or find a way how to achieve them usign the existing MAQL functions.
For the Age of a person, this might be reasonably close to what you want:

Copy code

```
SELECT
(THIS - Date (Birthdate))/365
WHERE Date (Birthdate)>(empty value)
+ 0*(COUNT(personID))
```

• THIS - Date (Birthdate) would be number of days since the birthdate
• divided by 365 would return this value in years with decimals
• the WHERE condition is there to exclude any instance where we would have empty birthdate
• and the 0*COUNT … is a trick to make sure we only get data for dates which really exist in the person dataset and not the whole date dimension. (but since we are multiplying it by 0 it will in no way affect the resultm

Mal Sharkey

02/03/2022, 12:05 PMHi **@Michal Hauzírek** - thanks for this. I needed to do an age calc today for asset, so I used your example. I found that this formula seemed to work for me:

Copy code

```
SELECT
(THIS - Date (AcquisitionDate)/365
+ 0 * COUNT(AssetId)
```

But when I add the where clause:
Copy code

```
SELECT
(THIS - Date (AcquisitionDate)/365
WHERE Date (AcquisitionDate) <> (empty value)
+ 0 * COUNT(AssetId)
```

I get an error:
Copy code

`Unexpected 'e'. Expecting: SELECT, RANKFUNCNAME, ZERO, FUNCNAME1, FUNCNAMEN, FUNCNAME2, TEXT_ELEM, OBJECT, FUNCNAME12, COUNT, SHORT_STRING, REPORT, PREVIOUS, PERCENTILE, IDENTIFIER, IFNULL, CASE, NEXT, NATURAL, STRING, IF, RUNFUNCNAME, (, REAL, ELEM_IDENTIFIER, -, THIS`

Any idea what's causing this? Thanks!m

Michal Hauzírek

02/03/2022, 1:15 PMHi **@Mal Sharkey**, it complains about the letter “e” so that brings me to suspicion that maybe you put the “(empty value)” there as a string?
You would need to select this value from the “Attribute Values” list in the metric editor, not enter it manually as text.
You will know you have it right when the value is orange in the metric editor, not black.

Michal Hauzírek

02/03/2022, 4:16 PMBTW it is not absolutely necessary to use the WHERE condition if Acquisition date would never be NULL/empty value.
I added it there because in case it is NULL, then for some technical reasons the difference

`THIS - Date (AcquisitionDate)`

returns a large number (number of days since 1900) and in case you have some aggregation above it, like average age, it can skew the result significantly.m

Mal Sharkey

02/03/2022, 9:50 PMAh - got it. Thanks!

p

Philippe Hébert

02/04/2022, 1:29 PM