topic Re: Create link table between 5 tables in QlikView

Create link table between 5 tables

sifatnabil — Tue, 25 Mar 2014 00:16:05 GMT

Hi,

I have 5 tables that have a common date. I have created a link table to create a star schema to join the dates, but while this is done, I need to associate a common ID between 2 tables. How can I include that in the star schema? The ID is only common between 2 tables out of 5.

The star schema looks like this:

Thanks.

Re: Create link table between 5 tables

Tue, 25 Mar 2014 00:18:51 GMT

You mean "Trade ID" in your example? If that is the case case I can see it is already associated.

Re: Create link table between 5 tables

sifatnabil — Tue, 25 Mar 2014 00:34:05 GMT

Hi Ramkumar,

No not TradeID, I wish to associate something else called DerivedTradeID. As long as I don't use the common field name "DerivedTradeID" in these 2 tables, I get the correct star schema. But the moment I make the common field name "DerivedTradeID" in TradeReport and GUIBOSReport tables, a synthetic key is created, and the table view looks like this:

Re: Create link table between 5 tables

Tue, 25 Mar 2014 00:41:01 GMT

that is because you now have more than 1 common column between 2 tables key and DerivedTradeId.

Now you have to have one mapping table to store the combinations of Key + DerivedTradeID.

Basically the new mapping table will contain 3 columns

Key

DerivedTradeID

SK (Surrogate Key)

SK will go into these 2 tables and Key will go into the rest of the 3 tables. Hope that makes sense. To generate SK use the function AutoGenerate.

We thus eliminate the problem of having more than 1 common column between tables and hence no synthetic keys.

Thanks,

Ram

Re: Create link table between 5 tables

sifatnabil — Tue, 25 Mar 2014 01:34:32 GMT

Hi Ram,

Thanks for your response. I deleted the link tables and created a new mapping table with the following:

TradeReport:

Date as Key,

[Trade ID] as DerivedTrade_ID,

Autonumber([Trade ID]) as SK

FROM..

GUIBOSReport:

Date as Key,

'R006' & right(EXTERNAL_ID,6) as DerivedTrade_ID,

Autonumber('R006' & right(EXTERNAL_ID,6)) as SK

Note that the DerivedTrade_ID in the GUIBOSReport is equal to the [Trade ID] in TradeReport once the manipulation above ('R006'..etc) is done. But I am stuck on how to create the Mapping table..I would initially write something like this:

MappingTable:

Key,

DerivedTrade_ID,

FROM ??

Thanks for your help on this.

Re: Create link table between 5 tables

Tue, 25 Mar 2014 01:43:04 GMT

No worries mate. Can you mark this thread as Answered then?

Thanks,

Ram

Re: Create link table between 5 tables

sifatnabil — Tue, 25 Mar 2014 02:19:52 GMT

Sorry Ram,

I think you missed it but I couldn't form the mapping table. I would start it like this:

MappingTable:

Key,

DerivedTrade_ID,

FROM ??

But I don't know where to source those fields from. Since Key belongs to 3 tables and SK belongs to 2 tables and DerivedTrade_ID belongs to those same 2 tables. I'm actually unsure of the logic behind this at the moment.

Re: Create link table between 5 tables

Tue, 25 Mar 2014 02:38:27 GMT

OK. On top of your load scripts for tables TradeReport and GuiBosReport you will have to write something like this.

TradeReport:

load autonumberhash256(key, derivedtrade_id) as surrogate_key,*;

load blah blah ...

<your source statement>;

GuiBosReport:

load autonumberhash256(key,derivedtrade_id) as surrogate_key,*;

load blah blah

<your source statement>;

And you form the mapping table like this.

MappingTable:

load distinct surrogate_key, key, derivedtrade_id , blah blah blah

resident TradeReport;

concatenate(MappingTable)

load distinct surrogate_key, key, derivedtrade_id, blah blah blah

resident guibosreport;

drop fields key, derivedtrade_id from TradeReport;

drop fields key, derivedtrade_id from guibosreport;

Hope that makes sense.

Thanks,

Ram

Re: Create link table between 5 tables

sifatnabil — Tue, 25 Mar 2014 03:36:01 GMT

Hi Ram,

I get the error "fields must be unique within the table" when I try this:

TradeReport:

Date as Key,

[Trade ID] as DerivedTrade_ID

AutoNumberHash256(Key, DerivedTrade_ID) as surrogate_key,*, <--also, what does * do?

Re: Create link table between 5 tables

vikasmahajan — Tue, 25 Mar 2014 03:41:11 GMT

You need to find common fields between all 5 tables

Identify Which are dimensions and facts tables

You need to create link table

I am attaching one sample demo for better understanding how to link this tables.

Hope this may help you

Vikas

Re: Create link table between 5 tables

Tue, 25 Mar 2014 04:12:38 GMT

Instead of * provide individual columns.

Auto number creates a consistent key based on the arguments.

Sent from my iPhone

Re: Create link table between 5 tables

Tue, 25 Mar 2014 04:25:57 GMT

Actually we are trying to avoid the synthetic key problem here.

So we combine Key and Trade id to form a new surrogate key. Once we form it we delete the key and trade id columns from the respective tables, so they exist only in the mapping table.

Duplicate error is because we selected key, traded and *. Give individual column names and that will be fine.

Re: Create link table between 5 tables

sifatnabil — Tue, 25 Mar 2014 05:01:28 GMT

Thanks Ram - this works if I concatenate the 3rd table in MappingTable, but when I try to concatenate the 4th table (AnnulmentReport), the PricingReport disassociates like below (note that PricingReport has only "Date" as common field). How can I concatenate the 3 extra tables in the MappingTable?

Re: Create link table between 5 tables

Tue, 25 Mar 2014 05:07:12 GMT

if the PriceReport has got only Date why are you bringing that into the Mapping Table. I suggested the Mapping Table just for the 2 Tables that you mentioned initially that shared Key and DerviedTrade_id. If you have the 3rd table that has the same 2 columns, you can bring that in using the same mechanism. But don't bring the PriceReport into this mapping table. It can exist on its own.

Thanks.

Re: Create link table between 5 tables

sifatnabil — Tue, 25 Mar 2014 05:15:41 GMT

I see what you mean. But if I leave the PricingReport on its own, its Date field won't have any association to the TradeReport and the GUIBOSReport. Essentially, I would like to attach 3 more tables like this that share only the Date field to the TradeReport and GUIBOSReport (which are already linked above). How could I do that?

The tables are like this:

Table1 - Date, TradeID

Table2 - Date, TradeID

Table3 - Date

Table 4 - Date

Table 5 - Date

Re: Create link table between 5 tables

Tue, 25 Mar 2014 05:23:17 GMT

Ok. Have one table for Date alone

Have one mapping table as follows: Date, TradeID, SurrogateKey

Now remove Date and TradeID from Table 1 and Table 2.

Now it will be like this.

Table1 and Table 2 are joined using mapping table. Mapping table is joined to the Date table using the Date. And Table 3, Table 4 and Table 5 are joined using the Date table.

Which means all the 5 tables are linked now.

Does that help?

Re: Re: Create link table between 5 tables

Tue, 25 Mar 2014 05:47:25 GMT

Attached the script and the resulting data model.

Hope that helps.

Re: Create link table between 5 tables

Tue, 25 Mar 2014 07:31:51 GMT

Hi Sifat,

Hope so it will help..

Do the following

Normal table

1-Key

2-Key

3-Key

4-Key+DerivedTrade as D_Key

5-Key+DerivedTrade as D_Key

Link_tbl:

1-Key

D_key

2-Key

D_key

3-Key

D_key

4-Key

5-Key

> Make use of Null() in place of field which is not available while creating Key in Link_table 4 & 5 .

Thanks & Regards,

Harshal

Re: Create link table between 5 tables

sifatnabil — Thu, 03 Apr 2014 01:32:51 GMT

Sorry for the late reply Ram, but thanks this worked. I believe I understand joins and concats better now, cheers!

Re: Create link table between 5 tables

Thu, 03 Apr 2014 02:02:43 GMT

You are most welcome!

Cheers.