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