Tableau fact table data structure

Question

I have some movie information in a database, simplified it looks like this:

table movie
-----------
id     title
1234   batman
4456   spiderman


table movie_info
-----------------
id    movie_id   info_type_id    info
1     1234       1               USA
2     1234       1               Canada
3     1234       2               Action
3     1234       2               Drama
4     4456       1               France


table info_type
---------------
id   info_type
1    country
2    genre

What I'm trying to do is creating a map displaying where the movies are created and then trying to filter them by genre. The mapping part works but when I filter by genre it only shows all movies when I set genre to Null as filter..

I tried a lot of stuff already with calculated fields and joining the data source with custom SQL to itself on movie_id but nothing seems to be working. I would appreciate if anyone could point me in the right direction to tackle this problem.

Calculated fields: Production Country:

IF[info_type_id]=8
THEN [info]
END

Genre:

IF[info_type_id]=3
THEN [info]
END

Custom SQL (for genres):

SELECT movie_id, info AS genre
FROM movie_info
WHERE info_type_id=3

I added some information. It's pretty basic, I am new to this. — JochenDB, May 06 '14 at 07:23
The Production Country and Genre calculated fields work fine when used seperately but not together. — JochenDB, May 06 '14 at 07:33

score 1 · Answer 1 · answered May 06 '14 at 23:14

First: This is more of a comment than an answer, but your table layout is illogical.

The ID field is traditionally a unique identifier. Instead, you're using it to duplicate your info_type. I'd think it a typo except that you use the numeric value in your pseudo code section as info_type_id.

If you want to associate a numeric value, have a separate lookup table so:

Table Info_type

id name
1 country
3 genre

And you'd associate it the same as you did the movie_id so the info_type would be 2 or 1 rather than a text value

As to your filter, without code, I'm guessing what you're doing, but have the code follow this pattern:

If [filter] == "ALL"
  return all entries with an info_type of genre (whether you get them from an array or querying the DB (Select * from movie_info where info_type="genre"))
Else
  return all entries with the info value of [filter] (whether you get them from an array or querying the DB (Select * from movie_info where info_type="genre" and info="[filter]"))

The difference between the two actions is that the all acts on the info_type value while the other acts on the info value. The logic follows the same whether you're looping/selecting from an array or the database.

The data structure was indeed like you described it with the info_type in a seperate table. I thought it was more understandable this way but I editted the post to make it more logical. — JochenDB, May 07 '14 at 07:31

Tableau fact table data structure

1 Answers1