3 Replies Latest reply: Jan 19, 2010 8:26 PM by Martha Thompson RSS

    Link To One Table Twice

    Martha Thompson

      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

          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
              John Witherspoon

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