Kinimod24 Posted April 22 Share Posted April 22 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. Link to comment Share on other sites More sharing options...
Gaia Paolini Posted April 22 Share Posted April 22 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). Link to comment Share on other sites More sharing options...
Sooriya rangasamy Posted April 22 Share Posted April 22 @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. Link to comment Share on other sites More sharing options...
David Boot-Olazabal Posted April 22 Share Posted April 22 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 More sharing options...
Kinimod24 Posted April 22 Author Share Posted April 22 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. Link to comment Share on other sites More sharing options...
Kinimod24 Posted April 22 Author Share Posted April 22 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 More sharing options...
David Boot-Olazabal Posted April 23 Share Posted April 23 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now