New Topic

Community Topic Question Report Builder

Nathan Schroeder

NA Business Team

Assigned

Category Report Builder

Product Version 7.4

In list filter on comma seperated list

Phillip Lassen shared this question ago

Answered

Hello,

we have a asset database with a field "genre" storing a comma separated list of genres for each asset. This are also connected to purchases. How would you guys best filter on genre (more than one genre possible), if you as well need to sum up the revenue?

What I tried so far:

Building a relation table for genre and asset id which gives me the opportunity to create in list filter, but in that case the sum of the revenue is multiplied with the number of genre (purchase x number of genres)
Building a Filter using a SQL Query to get the distinct values of all the comma separated lists. In that case I don´t need to create a relation table and have a distinct filter list of possible genres and the revenue is summed up correctly, which is perfect, but I am not able to select more than one genre.

Any ideas?

The same question

Replies (6)

Nathan Schroeder ●

Hi Philip,

If you are on Yellowfin 7.4 then you could use our split line ETL step, which will be included in the next couple of builds. Outside of this, unfortunately there really isnt a good way within the application to achieve this so you will need to come up with create solutions outside, like the DB level solutions you are proposing.

If you were to build a relational table, you would want to create this table with only the derived columns so that revenue is still matched against the comma included column (in the original table), and the other comma separated columns can be employed in a separate join logic, preventing erroneous aggregation.

Let me know your thoughts.

Regards,

Nathan

Reply URL

Phillip Lassen ●

Hello Nathan,

thank you first of all for your fast answer.

Could you be so kind and explain this paragraph? Maybe with an example

Yes, I have already created the relational table I need. For example:

asset_id, genre
1234, acion
1234, comedy

The problem arises when joining these table. For example:

transaction_id, asset_id, genre, price
123, 321, action, 3.00
123, 321, comedy, 3.00
456, 321, action, 6.00
456, 321, comedy, 6.00

I have two transactions with different prices. Sum(price) returns me 18.00, but I expect it to be 9.00.

Best,

Phillip

Hello Nathan,

thank you first of all for your fast answer.

Could you be so kind and explain this paragraph? Maybe with an example

Yes, I have already created the relational table I need. For example:

asset_id, genre
1234, acion
1234, comedy

The problem arises when joining these table. For example:

transaction_id, asset_id, genre, price
123, 321, action, 3.00
123, 321, comedy, 3.00
456, 321, action, 6.00
456, 321, comedy, 6.00

I have two transactions with different prices. Sum(price) returns me 18.00, but I expect it to be 9.00.

Best,

Phillip

Reply URL

Nathan Schroeder ●

Hi Philip,

Do you have a few minutes to screen-share on this this week? If so let me know what time works for you.I am on United States Mountain Time.

Regards,

Nathan

Reply URL

Phillip Lassen ●

Hi Nathan,

that would be great. How about between 8am and 9am your time? Let me know if that suits you.

Best,

Phillip

Reply URL

Nathan Schroeder ●

Hi Philip,

Sounds good, I will send you an invitation for 9am tomorrow.

Regards,

Nathan

Reply URL

Nathan Schroeder ●

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!

Regards,

Nathan

Reply URL