Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to create link table for my data model

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.

link table for all table.png

1 Solution

Accepted Solutions
jaswantc
Creator II
Creator II


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;

View solution in original post

10 Replies
vikasmahajan

Study the attached data model and incorporate the same.

vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Not applicable
Author

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
jaswantc
Creator II
Creator II

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.

Not applicable
Author

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

jaswantc
Creator II
Creator II


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;

Not applicable
Author

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

jaswantc
Creator II
Creator II

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.

Not applicable
Author

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