11 Replies Latest reply: Dec 7, 2010 11:06 AM by Les Backman RSS

    Targets with two fields in common

      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

          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

            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

                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

                    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

                        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

                            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

                                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

                                    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:

                                     

                                     

                                    CUSTOMERALANALANDAVIDDAVIDJOHNJOHNPETERPETER
                                    PRODUCT GROUPSalesTargetSalesTargetSalesTargetSalesTarget
                                    CHOCOLATE2202502017.54
                                    FRUIT0102.8353.11504
                                      • Targets with two fields in common

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