How to make a Join (in Data model) where 'id' is not exists then join to other IDs

[#1]

Dear Expertis,

I have a one requirement about make a data model use the below requirements.

i have a two tables

Tabel1         Id variable having data like   'Ref_id','Mi_Ref'

id,

Name

Table2

id,           Id variable having data like  'Ref_id'

Adress

there are other Tables has to join to these two tables now my question is how to join(in data model) table1 and Table2 where 'id' is not exists then Mi-Ref need to give the information in front end level or join to 'Mi_ref',obviously when id is exists then it will display all the related 'id' data in front side but i dont know how to show 'Mi_ref ' data when i select 'Mi_ref' because in the data model they are joined using 'Id's .i dont know does it make sense or not.

Please guide me how to solve this.

Regards,

Ram

[vinieme12]

Table1:

Load

Subfield (id, ',') as id

Name

From xxxxxx;

Table2:

Id, Address

From yyyy;

[Anonymous]

You could try using a link table using Vineeth's logic.

Something like:

Tabel1: 

load    

id as Table1ID,

Name

from xxxxx;

LinkTable:

Load

Table1ID,

Subfield (Table1ID, ',') as Table2ID

Resident Table1;

Table2

id as Table2ID,      

Addresss

from xxxxxxxx;

[vinieme12]

Why do we need a link table when we have a direct association??

[#5]

Hi ,

Thanks for getting back to me , i will elaborate more  my requirements i have a data model like below

Tables:

so i want to join 'comtable' into my data model ,comtable contains combinations of values like 'RET100' and '111a' these are te information avaiable in different tables like 111a-AGTable  and RET100-CTable.

So how do i join comtable into other table? if i join to  Ctable i wouldn't get AGTable data

OR

join to AGTable  ,not able to get Ctable data in front end charts right,

how do i resolve this .

As per your logic if i create link table it will be forming circular reference right(it was my mistake didnt expalin clearly )

[vinieme12]

Don't use join use apply map instead

Map comtable value for the other tables

[vinieme12]

Don't join - use Applymap instead

[Anonymous]

Well, I'm assuming it can't be both the A_ID,D_id, and C_id simultaneously.

So I would think you could just add a field to your fact table named ID.  Calculated something like:

=if(not isnull(D_id),D_id,

     if(not isnull(A_ID), A_ID,C_id))

[#9]

i want to visualise more charts based on Comtable so i need to keep it that table and get  more variable from that table.is there any other alternate ?

[Anonymous]

Good call.  If you really are linking tables with just 1 field definitely go for applymap!