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

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

Wed, 25 Nov 2020 16:16:04 GMT

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

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

vinieme12 — Thu, 26 Jan 2017 19:59:28 GMT

Table1:

Load

Subfield (id, ',') as id

Name

From xxxxxx;

Table2:

Id, Address

From yyyy;

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

Anonymous — Thu, 26 Jan 2017 20:16:20 GMT

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;

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

vinieme12 — Thu, 26 Jan 2017 20:55:23 GMT

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

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

Thu, 26 Jan 2017 21:02:09 GMT

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

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 )

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

vinieme12 — Thu, 26 Jan 2017 21:08:16 GMT

Don't use join use apply map instead

Map comtable value for the other tables

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

vinieme12 — Thu, 26 Jan 2017 21:10:48 GMT

Don't join - use Applymap instead

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

Anonymous — Thu, 26 Jan 2017 21:13:19 GMT

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

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

Thu, 26 Jan 2017 21:13:40 GMT

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 ?

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

Anonymous — Thu, 26 Jan 2017 21:14:24 GMT

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

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

vinieme12 — Thu, 26 Jan 2017 21:17:17 GMT

Having island tables is not recommended

Can you post excel file with dummy data for each table

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

Thu, 26 Jan 2017 21:22:20 GMT

Hi Atkinson,

this solution is very close to me but i am not able go through it ,is that possible can you please guide me on my sample application ,I am attaching sample for your reference.

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

Thu, 26 Jan 2017 21:24:01 GMT

yes Atkins you are right and i have more fields in that table for your example i have keep it only 2 fileds

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

Thu, 26 Jan 2017 21:24:32 GMT

Please find attached Sample Apps and Sample data

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

Anonymous — Thu, 26 Jan 2017 21:43:47 GMT

See if this meets your need.

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

Fri, 27 Jan 2017 10:56:06 GMT

Hi Atkinson ,

Unfortunately the RET selection doesn't working

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

Anonymous — Fri, 27 Jan 2017 15:53:54 GMT

It's because the RET isn't on the Fact table. I thought you wanted to link through the fact table?

Otherwise, you could do something like the attachment. But it may or may not work depending on your data.