4 Replies Latest reply: Sep 6, 2016 7:38 AM by Massimo Grossi RSS

    Need Help in Scripting

    Ramu Subramaniyan

      Hi Expert

       

      I have product list with 63 items (fixed). and customer list is 150 (fixed)

       

      and  i have trans table as below.Each customer would not get transaction for all the product items (that means 63) each month. In trans table i have to add the remaining(missing) product items for each customer though transaction made or not.

       

      if i make my master table [150 customer * 63 product items] and then right join with trans table  i would achieve the result. But it is increasing my master rows. There is any way only with product table i can reaming items in trans table for each customer

       

      Please help me how can we achieve it script

       

      TransTable

      MonthProduct CustomerAmt
      AprilP001C0015
      AprilP002C0011
      AprilP003C0011
      AprilP004C0011
      AprilP001C0021
      AprilP002C0021
      AprilP004C0021

       

      Regards

      Ramu

        • Re: Need Help in Scripting
          Tresesco B

          You can probably use left join with master table instead.

          • Re: Need Help in Scripting
            Anil Babu Samineni

            You can try with left join

             

            Main:

            Load * from source;

             

            Left join(main)

             

            Load *, fields from master calendar ;

            • Re: Need Help in Scripting
              Andrew Walker

              Hi Ramu,

                                  What about something like:

               

              Temp:

              Load

              Product,

              Customer,

              0 as Zero,

              'April' as Month

              Resident Master;

               

              Left Join(Temp)

              Load * Resident TransTable;

               

              TransTable1:

              Load

              Month,

              Product,

              Customer,

              RangeSum(Amt, Zero) as Amt

              Resident Temp;

               

              Good luck

               

              Andrew

              • Re: Need Help in Scripting
                Massimo Grossi

                test data (products, customers)

                Products:

                // P001 to P006

                load 'P00' & rowno() as Product AutoGenerate 6;

                Customers:

                // C001 to C005

                load 'C00' & rowno() as Customer AutoGenerate 5;

                 

                // your data, I added one row, may

                TransTable:

                load * inline [

                Month, Product, Customer, Amt

                April, P001, C001, 5

                April, P002, C001, 1

                April, P003, C001, 1

                April, P004, C001, 1

                April, P001, C002, 1

                April, P002, C002, 1

                April, P004, C002, 1

                May, P004, C002, 1

                ];

                 

                // all months x products x customers

                Final:

                LOAD Distinct Month Resident TransTable;

                join (Final) LOAD Product Resident Products;

                join (Final) LOAD Customer Resident Customers;

                 

                // add trans table amount

                left Join (Final) load * Resident TransTable;

                DROP Table TransTable;