topic Link To One Table Twice in QlikView

Link To One Table Twice

mjt_childrens — Tue, 19 Jan 2010 20:44:36 GMT

I am new to Qlikview scripting and stuck on an issue. I have included a simplified diagram. In the Orders table I need to obtain Customer information for both the Cust_No (sold to cust) and Bill_Cust fields in the Orders table.. I could load the customer table again, naming it Bill_Customers but that seems quite inefficient. What is the best way to handle this situation? Thanks.

Link To One Table Twice

hector — Tue, 19 Jan 2010 21:02:36 GMT

Hi, you can re-use a table using the resident sentence. Resident will load the data from the memory, so you don't need reload data from the source twice.

Just be careful with the name of columns (because in resident tables, the names of columns will be equal), your options are aliases or qualify the new table (and obviously unqualify the key field)

rgds and good luck

Link To One Table Twice

johnw — Tue, 19 Jan 2010 22:35:00 GMT

Put another way, YES, a common solution here is to have two customer tables. One would be keyed by Cust_No, the other by Bill_Cust. To avoid the inefficiency of loading from your database twice, you load the Billing Customers table from the already-loaded Customers table. Here's one possible approach (untested):

Orders:
LOAD
Order_No
,Cust_No
,Bill_Cust
<from your source>
;
[Sold to Customers]: // At this point, table includes ALL customers
LOAD
Cust_No
,Cust_Name
...
<from your source>
;
[Billing Customers]:
LOAD
Cust_No as Bill_Cust
,Cust_Name as Bill_Cust_Name
...
RESIDENT [Sold to Customers]
WHERE EXISTS(Bill_Cust,Cust_No) // This restricts the table to only customers used as billing customers
;
INNER JOIN ([Sold to Customers]) // This removes everything but sold to customers from the table
LOAD distinct Cust_No
RESIDENT [Orders]
;

Link To One Table Twice

mjt_childrens — Wed, 20 Jan 2010 01:26:49 GMT

Thanks for the information. I appreciate your help.