3 Replies Latest reply on Jul 23, 2018 7:18 AM by meenu choudhary

Create calculated field on top a Dimension table's column

Ayan Chakraborty Jul 23, 2018 1:27 AM

Hi Friends,

I am trying to make a calculated field on top of a dimension table to get the number of rows from that particular table.

Let me describe it a bit more.

1. I have a user table which have 80 users in that table.

2. In the fact table I have 100000 records, but, in between this 80 users there are only 30 user id is present in the fact.

Now I am trying to built a count of total user(eventually column is from the user table), but it is returning 30. I am really surprised. Please guide me.

**Note** The column name in both the table is different.

1. Re: Create calculated field on top a Dimension table's column

Naveen B Jul 23, 2018 2:40 AM (in response to Ayan Chakraborty)

Hi Ayan,

Which Join you are using- Kindly use full outer join so that you will get a cross product when you do the count you will be able to get 80

Kindly mark this answer as complete/helpful so that it will help others
Like Show 0 Likes(0)

Actions
2. Re: Create calculated field on top a Dimension table's column

Okechukwu Ossai Jul 23, 2018 4:21 AM (in response to Ayan Chakraborty)

You are getting only 30 users possibly because you are doing an inner join. Another alternative will be do a left join, since your User table contains a complete list of valid users.

Add the User table first on the left most side of the data join and then left-join all other tables to it.
Like Show 0 Likes(0)

Actions
3. Re: Create calculated field on top a Dimension table's column

meenu choudhary Jul 23, 2018 7:18 AM (in response to Ayan Chakraborty)

Hi Ayan,

Looks like you are using an inner join(or left join keeping fact table as left table).

If you want to have all the user go for left/right join keeping dimension table as left/right table.
Like Show 0 Likes(0)

Actions