Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

16 Replies
vinieme12
Champion III
Champion III

Table1:

Load

Subfield (id, ',') as id

Name

From xxxxxx;

Table2:

Id, Address

From yyyy;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

Hi ,

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

Datamodel.png

Tables:

Table.png

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
Champion III
Champion III

Don't use join use apply map instead

Map comtable value for the other tables

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

Don't join - use Applymap instead

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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