Multiple data table joins not working for crosstable

[Kinimod24]

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.

 

 

[Gaia Paolini]

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).

[Sooriya rangasamy]

@Gaia Paolini: Not sure what is the mapping in your datacanvas would be causing you the issue. Pls try to add them as well or the datasource. 

[David Boot-Olazabal]

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

[Kinimod24]

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).

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. 

 

[Kinimod24]

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  

[David Boot-Olazabal]

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):

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