model development
Hello,
I'm developing a model from an oracle connection (two tables). I'm trying to append one of the tables via union however when I do I only get a measure from one table and not the other. I need both. Any help with this would be greatly appreciated.
Thank you!
19 replies
-
Hi
To better understand your needs, could you please elaborate on your requirements? Specifically, when appending two tables, the columns must align for the data to merge correctly. Therefore, the measure you referenced will be added to the corresponding column. Alternatively, are you aiming to create a single table that combines distinct measures from each of the original tables?
thanks
Mark
-
yes so i have data from table 2 with a different measure that i need to append to table 1 that also has a measure...so i am trying to link revenue data to financial data from two different tables. A join does not work. When I previously created it in SAS append worked. I tried to do a Union with a small sample. If I create the measure column in Table 1 (measure table 2) it says the format is incorrect, I'm assuming because it's blank?
Basically, I'm matching student data from one table to the next, while i need the two to match i also need the data from the financial aid table that does not match but added on even if there is no corresponding revenue data.
-
How I want it to look
Tuition Titles TOTAL_Revenue TOTAL_Aid None - 500,000 Other Tuition Program Revenue 100,000 50,000 Other Tuition Program Revenue-Fall 200,000 50,000 
and how its actually looking:
Tuition Titles TOTAL_Revenue TOTAL_Aid None 600,000 - Other Tuition Program Revenue 100,000 - Other Tuition Program Revenue-Fall 200,000 - 
-
-
Hi
Also please make sure the columns are in the same order in both source nodes of the union.
-
thank you I did that but then it says the format is incorrect because I had to create columns without data in it...I don't know how to correct that...
-
I made the all the columns the same but when I append the measure from table 2 is not lining up with table 1...This is how I want it to look
Tuition Titles TOTAL_Revenue TOTAL_Aid None - 500,000 Other Tuition Program Revenue 100,000 50,000 Other Tuition Program Revenue-Fall 200,000 50,000 
and this is how it is looking:
Tuition Titles TOTAL_Revenue TOTAL_Aid None 600,000 - Other Tuition Program Revenue 100,000 - Other Tuition Program Revenue-Fall 200,000 - 
-
Looks like the order of columns might be out of order - can you share some screen shots of the data flow - particularly of the nodes entering the union nodes with their columns?
-
you were right but when i tried to fix it it says the column nodes do not match...but when i try to reorder it says they dont match...
-
I've been trying to do a work around, but I can't get the case statement to work either...
Case( Criteria(NotEmpty([Term_ID]) AND NotEmpty([Tuition_Titles]) AND NotEmpty([Level_Code_Description])), CriteriaResults(Concat([Term_ID], "-", [Tuition_Titles], "-", [Level_Code_Description])), Criteria(NotEmpty([Term_ID]) AND NotEmpty([Tuition_Titles])), CriteriaResults(Concat([Term_ID], "-", [Tuition_Titles])), Criteria(NotEmpty([Term_ID])), CriteriaResults([Term_ID]), "No Match" )
-
It looks like the error you're seeing is because the 'TOTAL_AMT' column and the 'Total aid' column have different data types – one's text, and the other's a number.
To fix this, you can use the 'Convert Column' node. Just add it to the part of your flow that's producing the 'TOTAL_AMT' column, and set it to convert that column to a double. That way, it'll match the 'Total aid' column.
Another way to solve this, if you're calculating 'TOTAL_AMT' in a calculated column, is to make sure the output data type is set to 'double'.
So, you can either use the 'Convert Column' node like we discussed, or adjust the data type in your calculated column. Both methods will ensure the columns match before you combine the tables.
Hope that gives you some options! Let me know if you have any other questions.
Thanks, Mark
-
Hi Mark. So the total values matched up, now that i've added calculated columns in the second table that the first table has the data is not aligning. when i create the column via calculated column what value should i use? right now i put "0" which i think is causing the problem.
an example is table one has campus, table 2 does not so i added it via calculated column with "0" value. when I create a discovery and filter the data by campus the revenue and aid do not match to the campus...
-
Hi could you put some screenshots to explain the example more for that scenario, i.e. the data flow and a discover showing the incorrect total by Campus
many thanks
Mark
-
The Union is separating the campus where the tuition is correct by campus but does not join the Aid by campus, just has campus as 0 (should be either NYC, PLV, WP) which I suspect has to do with the value given in the calculated column to add it to the Aid Table so that they will join...
-
The absence of a 'Campus' attribute in the Aid table suggests that aid is being treated as a global total. Is this the intended behavior, where the aid amount remains constant regardless of the selected campus? If campus-specific aid details are required, I recommend restructuring the model into two distinct fact tables: one for fees and one for aid. A simplified model illustrating this approach is provided below.
let me know if I have interpreted your requirement correctly
thanks
Mark
-
not all of the attributes exist in both tables therefore I had to create columns in the financial aid table in order to do a Union. I need all of the attributes to line up with the Term_IDs so that I can see all of the data by student - two different tables that need to join however there are students in the Fin Aid table that do not exsist in the Revenue table hence the union (which is how I originally create the model in SAS). I'm not sure how to restructure the model as you've stated above.
-
To ensure the Total Amount and Financial Aid Amount appear on the same row, they must share a dimensional relationship. In your example, you're using Tuition Titles and Term_ID, but the arbitrary assignment of Campus for Financial Aid prevents it from aligning with the Total Amount when a specific campus, like NYC, is selected. Since Financial Aid isn't directly related to a campus, this dimensional mismatch occurs. Removing the Campus dimension from your Discover report should resolve the issue and display the correct numbers.
To resolve this, I would avoid combining them in the data flow. Instead, You should create separate dimension tables to house the common dimensions, allowing them to be shared. Then establish the necessary relationships between these tables using the modeling tool.
If you could provide a sample dataset with anonymized data, I could demonstrate this process with a specific example.
thanks
Mark.
-
Thanks. I need to see the combination of data and be able to slice/dice by many elements that are in the revenue table but not in the financial aid table. So then how would I join them to match and include the additional data from the financial aid table. I was trying to create a CASE statement but was unsuccessful.
-
Is there a way to do this via join with a case statement?
-
-
Thank you! That worked!!!