Avoiding loops in linking tables

[#1]

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)

[#2]

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.

[#3]

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

[#4]

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

[#5]

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

[#6]

Hello Before the DROP Table(sets) you need to save it somewhere and drop for which you need to write the RESIDENT Sales(To save it in the sales table) before the Drop Table.

[#7]

I thought resident is used for retrieving data already loaded, not to save the data.

[#8]

Aplogise you need to JOIN and not use the Resident, even I am new to QV and learning the hard way by answering queries on Forums. After your 2 tables try this JOIN (Sales) Load * from Sets.xls... this solves your problem, this way the sets will be available in the Sales 🙂

ANDY

[#9]

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

[#10]

We are making progress, however the chart that i want to create needs to have SUPPLIER SET and CUSTOMER as dimensions,

If i want to display actual and target sales data, since the customer field from the target table has been renamed, all target values come up as zero for the customers