topic Avoiding loops in linking tables in QlikView

Avoiding loops in linking tables

Fri, 11 Feb 2011 15:55:34 GMT

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)

Avoiding loops in linking tables

Fri, 11 Feb 2011 16:00:37 GMT

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

Fri, 11 Feb 2011 16:09:01 GMT

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

Fri, 11 Feb 2011 16:20:24 GMT

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

Fri, 11 Feb 2011 16:36:52 GMT

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

Fri, 11 Feb 2011 16:58:13 GMT

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.

Avoiding loops in linking tables

Fri, 11 Feb 2011 17:11:12 GMT

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

Avoiding loops in linking tables

Fri, 11 Feb 2011 17:34:42 GMT

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

Avoiding loops in linking tables

Fri, 11 Feb 2011 17:50:11 GMT

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

Avoiding loops in linking tables

Fri, 11 Feb 2011 17:52:58 GMT

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

Avoiding loops in linking tables

Fri, 11 Feb 2011 20:54:05 GMT

Hi,

Nothing seems to be working, this is what i would like to obtain as my final chart, i can either get the sales to appear, or the targets to appear but not both

Avoiding loops in linking tables

johnw — Sat, 12 Feb 2011 00:08:48 GMT

As new_user3 suggested, left join your supplier sets onto your Sales table. Yes, you get a synthetic key. That synthetic key tells you that the relationship between sales and targets are by supplier set and customer. That is your intended relationship. The synthetic key is therefore relating your data the way you intended it to be related. It's not a problem; it's a solution.

See attached.

Avoiding loops in linking tables

Mon, 14 Feb 2011 15:48:37 GMT

Thank you very much to you and new_user3!