topic Targets with two fields in common in QlikView

Targets with two fields in common

Thu, 02 Dec 2010 23:34:10 GMT

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!

Targets with two fields in common

Thu, 02 Dec 2010 23:39:16 GMT

Do you need the supplier group field in table 3 when a supplier always has the same supplier group and that supplier is already linked to the supplier group in table 2?

Or is the sales target for the supplier group in which case the customers with a supplier belonging to the supplier group are already linked through table 2.

Targets with two fields in common

Thu, 02 Dec 2010 23:51:49 GMT

The sales target is for the supplier group, any ideas?

Targets with two fields in common

Fri, 03 Dec 2010 08:19:39 GMT

I don't know how the exact data model looks, but can´t you simply leave out customer name in table 3?

Targets with two fields in common

Fri, 03 Dec 2010 08:38:39 GMT

Hi,

If you want to break the circular loop you can rename(Alias) your field name.

It will be easy for me to understand the structure if you are able to post some sample data not actual .

Regards,

Ravi

Targets with two fields in common

Fri, 03 Dec 2010 14:55:11 GMT

Sorry I have not been so clear. EACH customer will have a seperate target for EACH product group.

I have attached sample data in an excel files, each sheet would be a different table.

Thanks again,

Targets with two fields in common

Mon, 06 Dec 2010 09:25:28 GMT

You could just load the 3rd table as under

LOAD CUSTOMER,
[PRODUCT GROUP] as CustomerProductGroup,
TARGET
FROM

Such renaming the field would remove your circular reference problem

Targets with two fields in common

Mon, 06 Dec 2010 15:57:24 GMT

While this does remove the circular reference, my problem has not been solved.

I would like my output table to be

Column 1: Product Group, Column 2: Customer Sales, Column 3: Customer Target,

I use the renamed Product group as the dimension and so it is linked to the customer targets. However the customer sales are no longer associated to this dimension.

Targets with two fields in common

Tue, 07 Dec 2010 10:52:01 GMT

Les,

Would be helpful if you could shows us a sample output which you want from the example which you had uploaded.

Targets with two fields in common

Tue, 07 Dec 2010 14:35:47 GMT

Here is the basic version of what I would like my output to be. I have no issues getting my chart to look like this, it is only making the correct associations. As you can see, the sales and the target amount must be associated to the product group. Thanks again for the help

Targets with two fields in common

Tue, 07 Dec 2010 14:58:49 GMT

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

Targets with two fields in common

Tue, 07 Dec 2010 16:06:59 GMT

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$);