Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Study the attached data model and incorporate the same.
vikas
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.
- 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
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.
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
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;
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
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.
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