Targets with two fields in common

[#1]

Hi everyone,

I am trying to load into my document different sales targets for customers based on a group of suppliers.

In table 1, I have the sales data (which includes a field for customer, supplier, sales amount ...etc),

In table 2, I have my two fields, supplier name and the supplier group name they belong to

In table 3, I would have my customer name, supplier group name and sales target

because table 3 has connections to table 3 (supplier group) and table 1 (customer name) a circular reference occurs.

Can someone please help me overcome this?

Thanks!

[#2]

Hi Les,

Here's what you can do

Script:

Sales:
LOAD DATE,
CUSTOMER,
INVOICE,
PRODUCT,
PRICE,
QUANTIY,
TOTAL
FROM ABC.xlsx (ooxml, embedded labels, table is Sheet1);


temp:
LOAD PRODUCT,
[PRODUCT GROUP]
FROM ABC.xlsx (ooxml, embedded labels, table is Sheet2);

left join (Sales) load * resident temp;
drop table temp;

Target:
LOAD CUSTOMER,
[PRODUCT GROUP],
TARGET
FROM ABC.xlsx (ooxml, embedded labels, table is Sheet3);

The output looks as under:

CUSTOMER	ALAN	ALAN	DAVID	DAVID	JOHN	JOHN	PETER	PETER
PRODUCT GROUP	Sales	Target	Sales	Target	Sales	Target	Sales	Target
CHOCOLATE	2	2	0	25	0	20	17.5	4
FRUIT	0	10	2.8	35	3.1	15	0	4

[#3]

Hi Umang,

It does not seem to work with that, my targets are all coming up at 0....here is my actual code that I used and an image of the tables that are produced...

product group = supplier set and product = supno

Sales:
LOAD
 CUSTNO,
 CUSTNAME,
 [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\IN2010.qvd]
(qvd);

temp:
LOAD [SET]&'-'&[SET TITLE] AS [SUPPLIER-SET],
 SUPNO
FROM
[..\Data\Supplier Sets.xls]
(biff, embedded labels, table is [SUP-SET$]);
left join (Sales) load * resident temp;
drop table temp;
 target:
 LOAD
 [SUPPLIER-SET],
 CUSTNO,
 [CUSTOMER-TARGET]
FROM
[..\Data\SUMMIT TARGETS.xls]
(biff, embedded labels, table is Sheet1$);