New Topic

Community Topic Question Report Builder

Nathan Schroeder

NA Business Team

Assigned

Category Report Builder

Product Version 7.3

Use subquery's calculated field in master's calculated field

Xuan shared this question ago

Answered

I'm having a hard time to create a calculated filed in master query.

Details:

The calculated filed in master query uses fields from two different subqueries, one is a calculate dfiled in a subquery, the other one is just a normal date field in subquery. For example,

forecast_date: date field in forecast subquery

plan_date: calculated filed in plan subquery (view level)

actual_date: field in master query

and the final calculated filed that needs to be created in master query is:

final_date = case when actual_date is not null then actual_date else (case when forecast_date is not null then forecast_date else plan_date).

I have tried to create a calculate filed in master query for `case when forecast_date is not null forecast_date else plan_date`, however this calculated filed is not accessible when trying to create the final_date calculate field in master query.

Wondering if there's any workaround? Thanks in advance.

The same question

Replies (1)

Nathan Schroeder ●

Hi Xuan,

As mentioned in a similar ticket, if you are not able to achieve this through the current functionality (which is geared around constructing an SQL statement), then you will need to build these final calculations into advanced functions, which are applied in post-processing.

If our current advanced functions do not do the trick, you might want to look into coding your own advanced function:

http://wiki.yellowfin.com.au/display/USER74/Custom+Advanced+Functions

Regards,

Nathan

Reply URL

Xuan ●

Hi Nathan,

Thanks for your response, i tried to implement my own advanced function to combine multiple columns and have one question regarding function `getReturnType`, how to make the return type of this function the same as the column that this function is applied to? From the example in yellowfin's wiki, the return type is hardcoded to numeric, not sure which function to call to make the return type be the same as the column itself? Could you please advise?

Here's the logic of my function

```

case when columnA is not null then columnA else (case when columnB is not null then columnB else columnC).

```

And, columnA, columnB, columnC could be numeric, text or date column, the only requirement is they are the same type.

see below implementation as example:

```

public Object applyAnalyticFunction(int i, Object obj) throws Exception {
    Object[] arrayOfObject = (Object[])this.getParameterValue("concatenateColumn");
    if (obj == null) {
        if (arrayOfObject[i]!= null) return arrayOfObject[i];
        else return null;
    } else return obj;

}```

Hi Nathan,

Here's the logic of my function

```

case when columnA is not null then columnA else (case when columnB is not null then columnB else columnC).

```

And, columnA, columnB, columnC could be numeric, text or date column, the only requirement is they are the same type.

see below implementation as example:

```

public Object applyAnalyticFunction(int i, Object obj) throws Exception {
    Object[] arrayOfObject = (Object[])this.getParameterValue("concatenateColumn");
    if (obj == null) {
        if (arrayOfObject[i]!= null) return arrayOfObject[i];
        else return null;
    } else return obj;

}```

URL

Nathan Schroeder ●

Hi Xuan,

Unfortunately at the moment it is not possible to set one of these based on an input of the other, however we are planning to make some adjustments to the way that advanced functions work, and this may be included in that process.

One idea is to create separate advanced functions for each of the return types, and only show a one of the functions based on the column selected.

Sorry for the inconvenience.

Nathan

URL

Xuan ●

Hi Nathan,

Thanks for your reply. I have implemented custom advanced functions for numeric, text, date columns separately to workaround the issue.

URL

Nathan Schroeder ●

Hi Xuan,

Sounds good, closing this but please let me know if you run into any problems.

Nathan

URL