Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mjt_childrens
New Contributor III

Link To One Table Twice

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.

Tags (2)
1 Solution

Accepted Solutions
hector
Valued Contributor

Link To One Table Twice

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

3 Replies
hector
Valued Contributor

Link To One Table Twice

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

MVP
MVP

Link To One Table Twice

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]
;

mjt_childrens
New Contributor III

Link To One Table Twice

Thanks for the information. I appreciate your help.

Community Browser