how to create link table for my data model

[#1]

Hi All

http://community.qlik.com/thread/96092

I have started the above link , and chowdary manage to provide me solution on how to create a link table which working fine. when i try to do it on my actual QV doc , i am not able to do it. half way thru it take very long to reload the script. so i assume it is due to my code not so smart. ( as my sample data file is vey small )

now i hope some one can help me remove the syn key.

[vikasmahajan]

Study the attached data model and incorporate the same.

vikas

[#3]

Hi Vikas

The final part of the script is very powerful. which i see star.

LinkTable:

Load DISTINCT

  Year & '_' & Month & '_' & [Customer Number] as Key,

  Year,

  Month,

  [Customer Number]

FROM LinkTable.xls (biff, embedded labels, table is [Budget$]);

after reading thru the ppt and analysis the qv doc when i go back to my qv doc , i totally lost again. why i lost ?

1. your using 1 key to link sales table and budget table and customer table. too high level. i have issue to link key with 2 table,

Any way i learn one new thing :-

that is my individual key is wrong , as i only use cust_id with country name , where you using date. this make me realise how to create link key , must make use of date as well.

For this is need to do it in the morning , as now is evening , my mind is not so powerful . will try again morning wake up. thank you very much for your sharing.

[vikasmahajan]

- Hello some part of tab1 & 2 is commented because it is not required

   For Data Modelling following are the tips

- Identify common fields between common tables

- Create Key

- Create distinct Link tables

- Comments  common  fields which we have taken in link table

- load common table with key

- Finally see table Viewer for Link.

Hope now you confusion is clear  Good Luck.

Vikas

[Anonymous]

Hi Paul,

As Vikas told, that is standard to follow while creating link table.

- Identify common fields between common tables

- Create concatenated Key

- remove common fields from source tables and move them onto link table which we have taken in link table

- load common table with key

Can you tell us what are the key fields expected to link between Inventory, debt, sales and supplier tables.

Thanks,

JaswantC.

[#6]

Hi Chowdary

Total i have 3 link key from 3 table as following :-

(1) SALES TABLE & INVENTORY TABLE :-

From sales Table link key i use filed PRODUCT_CODE_SALES and SOURCE name :-

  autoNumberHash128(@152:166T,'TDSID') As LINK_KEY_INV,

  @152:166T as [PRODUCT_CODE_SALES],

  'TDSID' as SOURCE,

From inventory Table link key i use filed PRODUCT_CODE_INV  and SOURCE name :-

autoNumberHash128(@1:11T,'TDSID') As LINK_KEY_INV,

@1:11T as [PRODUCT_CODE_INV],

'TDSID' as SOURCE,

(2) SALES TABLE and BEDT TABLE :-

From sales Table link key i use filed CUST_ID and SOURCE name :-

  autoNumberHash128(@1:10T,'TDSID') As LINK_KEY_DEBT,

  @1:10T as [cust_id],

From DEBT Table link key i use filed CUST_ID_PAY  and SOURCE name :-

AutoNumberHash128(@1:9T,'TDSS') As LINK_KEY_DEBT,

@1:9T as [CUST_ID_PAY],

(3) SALES TABLE and SUPPLIER TABLE.

From SALES Table link key i use filed CUST_ID_PAY  and SOURCE name :-

  AutoNumberHash128(@50:60T,'TDSID') As LINK_KEY_SUP,

  @50:60T as [date],

From SUPPLIER Table link key i use filed CUST_ID_PAY  and SOURCE name :-

  AutoNumberHash128(@51:61T,'TDSID') As LINK_KEY_SUP,

  @51:61T as [date],

Thank you very much for your help. Enclosed the QV doc which i already created all the above mentioned link key.

Paul

[Anonymous · Accepted solution]

Hi Paul,

I understand that it appears that you should build your model as start schema, where sales as fact and rest of the tables like dimensions,.

Please find attached file where i did binary load of your file and below code, see if this works for you.

Drop

fields

LINK_KEY_INV

, LINK_KEY_DEBT

, LINK_KEY_SUP

;

debt1:
LOAD

Distinct

*,
CUST_ID_PAY

& '|' & SOURCE

AS

Link_DEBT

,
SOURCE

AS

debt_source

Resident

debt;

supplier1:
load

date

& '|' & SOURCE

as

Link_Supp

,
SOURCE

AS

supplier_source

,
*
Resident

supplier;


inventory1:
LOAD

PRODUCT_CODE_INV

& '|' & SOURCE

as

Link_INV

,
SOURCE

AS

inventory_source

,
*
Resident

inventory;


drop

Field

SOURCE

from

debt1;
Drop

field

date

,SOURCE

from

supplier1;
DROP

Field

SOURCE

from

inventory1;
drop

tables

debt, inventory, supplier;

sales1:
LOAD

PRODUCT_CODE_SALES

& '|' & SOURCE

as

Link_INV

,
date

& '|' & SOURCE

as

Link_Supp

,
CUST_ID

& '|' & SOURCE

as

Link_DEBT

,
*
Resident

sales;

Drop

fields

SOURCE

from

sales1;
drop

table

sales;

[#8]

Hi Chowdary

Wow you great , i must call you master of link table. you solve my problem. Now my table look so neat , it never happen before.

By the way it is a must to use binary load ? as i try to add link table on my existing script , i notice that it slow down lot , for example my record is very few , but it hung there for 5 sec. on below script :-

sales1:

LOAD

PRODUCT_CODE_SALES & '|' & SOURCE as Link_INV,

date & '|' & SOURCE as Link_Supp,

CUST_ID & '|' & SOURCE as Link_DEBT,

*

Resident sales;

My question it is better for me use binary load to aviod slow reloading of script ?

Once again thank you for your solution. i will need to go ahead to add GL table . this is the last and more complicate one. once it link , in future user can view all information with out need to click initial select again and again,

Paul

[Anonymous]

Hi Paul,

You don't need to do binary load, I used because i don't have your source files and hence used your model as a input.

You can add the below code to one your shared your file with earlier and re-load thats it.

Drop

fields

LINK_KEY_INV

, LINK_KEY_DEBT

, LINK_KEY_SUP

;

debt1:
LOAD

Distinct

*,
CUST_ID_PAY

& '|' & SOURCE

AS

Link_DEBT

,
SOURCE

AS

debt_source

Resident

debt;

supplier1:
load

date

& '|' & SOURCE

as

Link_Supp

,
SOURCE

AS

supplier_source

,
*
Resident

supplier;


inventory1:
LOAD

PRODUCT_CODE_INV

& '|' & SOURCE

as

Link_INV

,
SOURCE

AS

inventory_source

,
*
Resident

inventory;


drop

Field

SOURCE

from

debt1;
Drop

field

date

,SOURCE

from

supplier1;
DROP

Field

SOURCE

from

inventory1;
drop

tables

debt, inventory, supplier;

sales1:
LOAD

PRODUCT_CODE_SALES

& '|' & SOURCE

as

Link_INV

,
date

& '|' & SOURCE

as

Link_Supp

,
CUST_ID

& '|' & SOURCE

as

Link_DEBT

,
*
Resident

sales;

//Drop fields SOURCE from sales1;

drop

table sales;

Thanks,

JaswantC.

[#10]

Hi Chowdary

You must have a very powerful mind , you can work backward. you script work fine. can you share with me how to train my mind like your ?

Paul