Why arent you are doing Inner join or Right Join.Probably this will solve your problem
None of the options work. As a left join I get all the student names but the table is populated with NULL for those students where there are no payments on Table B.
I created a calculated field such as:
If [date] = NULL (e.g. there is no payment on table B) then just show 99 (to try and debug). But this didn't work either.
1 of 1 people found this helpful
Before you do a join you will want to reshape your Table A a bit, because at the moment it is a crosstab and this will prevent you from easily creating a join using the right dimensions. You will also want to make sure that the month when the tuition fee is due and the month when the payment was made are also in the same format (because they need to make part of a join condition between the two tables).
Once you have your dates in a consistent format you can reshape your data. If your data is in an excel file then you can have a look here about how to easily do that.
Then you can do a left join as you initially intended with two conditions:
A.Student = B.Student
A.PaymentDue = B.PaymentDateMonth
For your convenience I've attached:
Example source data.xlsx which contains Table A before and after the reshape and Table B
Example.twbx where you can see how the data source was built
Hope this helps
Yep this is the right approach, We can do the same thing by using the Pivot Feature as well which is available in tableau 9.Thanks for your solution
The problem is I cant change the data.
Figured a way that this works though without reshaping, as follows:
I've created 2 calculated fields:
SUM([Payments])-AVG(All Month Tuition Fees)
IF ISNULL(([xBalance])) THEN
-(AVG([All Month Tuition Fees]))
its a bit long winded but does the trick. Of course your version would be a lot slicker.