Jump to content
We've recently updated our Privacy Statement, available here. ×

Multiple data table joins not working for crosstable


Kinimod24

Recommended Posts

Hello All,

Wanted to see if there is a solution for this. I have 2 data tables. 

DATA TABLE 1  has 3 columns: ProjectID, WorkItemID, and Fixed Price

DATA TABLE 1  has 2 columns:  Project Code and Duration.

the relation and column match of these two tables is WorkItemID and Project Code. 

I want to build a crosstable based on ProjectID and Duration. but the Duration sums up and repeats for each ProjectID. It should be specific to that ProjectID only.

 

 

TEST.jpg

Link to comment
Share on other sites

you are getting this warning, (triangle top right). It looks like you would have to match a ProjectID column to make it work, but you don't have this column in the second table. It would work if you created a join between the two tables based on Work Item/Project Code (provided this is different for each Project ID).

image.png.f46887cf105525a50236e364c27828ca.png

Link to comment
Share on other sites

Hi Kinimod24,

Please have a look at the attached dxp example.
I have merged the two data tables in the Data canvas, joining in Table1.WorkItem=Table2.ProjectCode.

Whenever there are multiple work items per project id (data table 1), the total duration of the work items from data table 2 will be summed per project id. But if that is not the case (1 work item per project id), you will see the exact duration period back from data table2 in your newly created crosstable.

Kind regards,

David

 

Data Table merge.dxp

Link to comment
Share on other sites

8 hours ago, Gaia Paolini said:

you are getting this warning, (triangle top right). It looks like you would have to match a ProjectID column to make it work, but you don't have this column in the second table. It would work if you created a join between the two tables based on Work Item/Project Code (provided this is different for each Project ID).

image.png.f46887cf105525a50236e364c27828ca.png

I have created the Relation and Column Match between Work ItemID and Project Code. Each ProjectID has a unique Work ItemID/Project Code (Projectid would be P-12345, Work ItemID would be 456, Project Code would be 456). So the crosstable should work but it doesn't as you can see in my image. 

 

Link to comment
Share on other sites

7 hours ago, David Boot-Olazabal said:

Hi Kinimod24,

Please have a look at the attached dxp example.
I have merged the two data tables in the Data canvas, joining in Table1.WorkItem=Table2.ProjectCode.

Whenever there are multiple work items per project id (data table 1), the total duration of the work items from data table 2 will be summed per project id. But if that is not the case (1 work item per project id), you will see the exact duration period back from data table2 in your newly created crosstable.

Kind regards,

David

 

Data Table merge.dxp 325.29 kB · 3 downloads

Thank you for the reply. I looked at this file and noticed that this is adding going the "add columm" method with a join. The issue is that my 2 data tables have multiple fixed prices and durations per project in each table. The tables are huge. If I do an add column with a join it repeats a lot of data. I was hoping there would be a solution to keeping the data tables separate but only do a relation and column match  

Link to comment
Share on other sites

Hi Kinimod24,

The reason why you get the warning triangle, when using column matches, is the fact you don't have a match for project id (as Gaia Paolini mentioned).
Now, looking at my example data, this may not be an issue if the data is rendered correctly. But I can't speak for your dataset of course.

Here's what it looks like when I use column matches and set up a crosstab and barchart (both warning messages are the same):
image.thumb.png.022bbbdda11cd076a0814fd9ab26fd34.png

Relations do not work in this case, since that is only appropriate for filtering and marking. But column matches with the above caveat can work. If not, the only option is to merge the two tables into one dataset.

Kind regards,

David

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...