12 Replies Latest reply: Feb 14, 2011 10:48 AM by Les Backman RSS

    Avoiding loops in linking tables

      Hi Community,

      I have been having this issue for quite some time and just can't figure out my issue. (i'm sure it is fairly straight forward)

      In a nutshell, I have four tables: sales, targets, sets, and calendar. My issue is linking the sales, targets and sets tables without creating loops.

      Each sales has data pertaining to the supplier of the product. My sets table groups some suppliers into sets and my targets are customer AND supplier set specific. Please offer some help on how i should link all this. (color codes below show what have to me linked)

      error loading image

        • Avoiding loops in linking tables

          Hello,

          A quick solution (I would try if I was you) would be to merge the Sales and Sets table and then join the Sales table to Target using the SupplierSet# thereafter. However I think you need to check if that has an impact anywhere else in other tables.. But this is a real quick solution which I tried sometime back and seemed it worked. Even I am new to QV but its really good and Easy to use.

            • Avoiding loops in linking tables

              Thanks for the reply, i tried doing a concatenate of the sales and sets table but the issue with the targets table is that targets are for the customer and for the supplier set and so what happens is a $Syn Table is created

                • Avoiding loops in linking tables

                  Hello,

                  I mean to say you have something like Tablename and the load statement TARGET: Load * from Target, SALES: Load * from Sales, SETS: Load * from SETS (add this script at the end: Resident SALES DROP TABLE SETS;) after the load statement for SETS you are asking to store the data from that into the Sales table and then drop the SETS table. Then you would need to rename the Customer in the Target or Sales table to avoid loops and QV thereafter links both these tables with the SUPPLIER SET# automatically.

                  Please try around this then you might know whats happening and learn different ways.

                  ANDY

                    • Avoiding loops in linking tables

                      This is what i have:

                       


                      Sales:
                      LOAD
                      CUSTNO,
                      CUSTNAME,
                      [CUSTOMER LOCATION],
                      [INVC DATE],
                      SUPNO,
                      CATNO,
                      [NET PRICE],
                      [REG COST],
                      [COST UNIT],
                      [G/L COST],
                      AMOUNT,
                      [REG GP],
                      [SUP-ABBR],
                      SUPABRNO,
                      CUSTABRNO,
                      [CUST-ABBR]

                      FROM
                      [..\Data\QVDfiles\IN2011.qvd]
                      (qvd);
                      sets:
                      LOAD [SET]&'-'&[SET TITLE] AS [SUPPLIER-SET],
                      SUPNO

                      FROM
                      [..\Data\Supplier Sets.xls]
                      (biff, embedded labels, table is [SUP-SET$]);
                      DROP TABLE sets;
                      target:
                      LOAD [SUPPLIER-SET],
                      [CUSTOMER-TARGET],
                      CUSTNO as customer
                      FROM
                      [..\Data\SUMMIT TARGETS.xls]
                      (biff, embedded labels, table is Sheet1$);


                       

                      by dropping the table sets, the targets table stays unconnected. Thanks again for the help

                • Avoiding loops in linking tables

                  Hi,

                  Why dont you try Concatenating the three tables (Sets, Sales, Target). This way you can have all of your info in one table and link it to the master calendar.

                  It would end up like this:

                  Hope it helps