Create a straight table out of disconnected tables in data model

[#1]

Hi All,

Thanks for you time. Have simplified my data model in the example below for ease of explanation.I have three tables

Table 1 : Has fields Name1, Sales1

Table 2 : Has fields Name2, Sales2

Due to the complexity of the data model Table 1 and 2 cannot be connected by the field 'Name' although 'Name1' and 'Name2' have very similar values

Table 3 is an island that has only one field Name and it is not connected to any table.

I am trying to create a straight table that has the following :

1)  Dimension : Name

2) Expression 1: Sum(Sales1) where Name1=Name

3) Expression 2: Sum(Sales2) where Name2=Name

I have tried using expressions such as Sum({$<Name1=p(Name)>}Sales1) but I cannot get the total corresponding the value of Name listed in the dimension column of the straight table.

Also tried Sum({$<Name1={'$(=only(Name))'}>}Sales1) but shows up as an invalid expression. Any help regarding this would be great.

Thanks,

Neeraj

[Gysbert_Wassenaar]

A set analysis expression creates one set per chart. What you probably need is an if statement:

sum(if(Name1=Name,Sales1))

But perhaps you should consider concatenating Table1 and Table2. It may make the data island Table3 unnecessary.

[Colin-Albert]

The data in Table1 and Table2 should be concatenated so you have a single Table with Name and Sales fields.

You do not need two tables with Name1, Name2  Sales1, Sales2.

You will be able to sum your data from this single table. without needing a separate table for the Name dimension.

[#4]

You could try:

Dimension : Name

Expression 1: sum(if(Name1=Name, sales1))

Expression 2: sum(if(Name2=Name, sales2))

Best,

matt

[#5]

Hi Colin,

Thanks for your prompt response. In my actual data model Table 1 and Table 2 are linked by another Key . Also, in the actual data model Table 1 has 2 names OldName1 and NewName1 and there are two expressions related to table 1:

1) Sum(Sales1) where OldName1=Name

2) Sum(Sales1) where NewName1=Name

Which makes it necessary to have a data island as we need to conditionally link the field Name to two different field in table 1. Is there a work-around you would suggest ?

Thanks,

Neeraj

[#6]

Hi Gysbert,

Thanks for your prompt response. In my actual data model Table 1 and Table 2 are linked by another Key . Also, in the actual data model Table 1 has 2 names OldName1 and NewName1 and there are two expressions related to table 1:

1) Sum(Sales1) where OldName1=Name

2) Sum(Sales1) where NewName1=Name

Which makes it necessary to have a data island as we need to conditionally link the field Name to two different field in table 1.

In my actual expression has set analysis in it and looks like this

=count({$<NewName1=Name,FromDate={"<=$(MinDate)"},ToDate={">=$(MinDate)"}>} OwnerCaseId)

How can i implement the if condition into the set analysis.

Thanks,

Neeraj

[Gysbert_Wassenaar]

As I already mentioned I don't think you can use set analysis expressions. If you don't believe me you can try this:

=count({$<NewName1={"=NewName1=Name"},FromDate={"<=$(MinDate)"},ToDate={">=$(MinDate)"}>} OwnerCaseId)

[#8]

Sorry had missed the first line of your previous comment. Will try to use an if statement to solve the problem.

Thanks,

Neeraj