10 Replies Latest reply: Aug 29, 2010 5:16 AM by dacquet RSS

    Lastest value of fields

    dacquet

      Hi All,

       

      I would like to put the latest value of a field during the load.

       

      I have sales record :

       

      YYYY, MM, Product, Customer,SalesPerson, SalesTerritory, SalesGroup

      and I would like to update these record during loading script to update SalesTerritoty, SalesGroup field with their latest value so that we don't keep history.

       

      Thanks for your help ;)

       

       

       

        • Lastest value of fields
          John Duffy

          Hello.

          I'm assuming the lastest value would be determined by the maximum YYYY MM for each Product, Customer and SalesPerson. If so, try the following:

          Load YYYY& MM as YYYYMM, Product, Customer, SalesPerson, SalesTerritory, SalesGroup

          from TableA;

          inner join

          Load Product, Customer, SalesPerson, Max(YYYY&MM) as YYYYMM

          from TableA;

            • Lastest value of fields
              dacquet

              Thank you John but it does not update the field SalesTerritory, SalesGroup and create a syn table.

                • Lastest value of fields
                  John Duffy

                  Can you post a simple version of the applicaion using an inline load with a description of what you would like the resulting table to look like. Where are the new values for SalesTerritory and SalesGroup obtained?

                    • Lastest value of fields
                      dacquet

                      I have the following data

                       

                      LOAD * INLINE [
                      YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
                      200901, 10, 2, 3, 1
                      200902, 10, 2, 3, 1
                      201001, 14, 2, 4, 2
                      ];

                       

                      And I would like to have

                       


                      YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
                      200901, 14, 2, 3, 2
                      200902, 14, 2, 3, 2
                      201001, 14, 2, 4, 2

                      Only field SALESREP, SALESTERRITORY should take the latest value (we don't want history)

                        • Lastest value of fields
                          John Duffy

                          The following code will give you the desired results. I am assuming that the only field used to determine which Salesrep and Salesterritory to use is YYYYMM. Let me know if this helps:

                           

                           

                           

                           

                           





                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           



                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           



                           

                           

                           

                           

                           

                           

                           



                           

                          Temp_Test_Table:
                          LOAD * INLINE [
                          YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
                          200901, 10, 2, 3, 1
                          200902, 10, 2, 3, 1
                          201001, 14, 2, 4, 2
                          ] ;


                          Max_YYYYMM_Table:

                          LOAD MAX(YYYYMM) as MAX_YYYYMM
                          Resident Temp_Test_Table
                          ;

                          let vMaxYYMM = peek ('MAX_YYYYMM',0,Max_YYYYMM_Table) ;

                          Final_Values:
                          Load YYYYMM, SALESREP, SALESTERRITORY
                          Resident Temp_Test_Table
                          WHERE
                          YYYYMM = $(vMaxYYMM)
                          ;

                          Test_Table:
                          Load YYYYMM, CUSTOMER, PRODUCT
                          Resident Temp_Test_Table
                          ;

                          left join

                          Load SALESREP, SALESTERRITORY
                          Resident Final_Values
                          ;

                          Drop Tables Temp_Test_Table, Max_YYYYMM_Table, Final_Values ;

                           

                           







                            • Lastest value of fields
                              Dinesh Kumar

                              Nice logic John...!!!

                              • Lastest value of fields
                                dacquet

                                Thanks John. I think it will help. I use 2 fields to determine salesrep and territory : customer and YYYYMM

                                 

                                • Lastest value of fields
                                  dacquet

                                  Hi John,

                                   

                                  I tried this but it does not work nothing is in Final result.

                                  Can you help me ?

                                   

                                  Temp_Test_Table:
                                  LOAD * INLINE [
                                  YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
                                  200901, 10, 2, 3, 1
                                  200902, 11, 3, 3, 1
                                  201001, 14, 2, 4, 2
                                  ] ;


                                  Max_YYYYMM_Table:

                                  LOAD MAX(YYYYMM) as YYYYMM,
                                  CUSTOMER
                                  Resident Temp_Test_Table
                                  group by CUSTOMER;


                                  Final_Values:

                                  LOAD YYYYMM,
                                  CUSTOMER
                                  Resident Max_YYYYMM_Table;
                                  inner join
                                  LOAD YYYYMM,
                                  CUSTOMER,SALESREP, SALESTERRITORY
                                  Resident Temp_Test_Table;

                                  Drop Tables Temp_Test_Table;

                                    • Lastest value of fields
                                      John Duffy

                                      Hello.

                                      Since you are now obtaining the values for Sales Rep and Sales Territory for the max YYYYMM at the customer level, you do not want to use YYYYMM in your final join. You need to do an inner join to the main table with both YYYYMM and Customer to obtain the Sales Rep and Sales Territory values for each customer. Then you left join these values to the original table. I think the following code will give you the results you are looking for:

                                      Temp_Test_Table:
                                      LOAD * INLINE [
                                      YYYYMM, SALESREP, CUSTOMER, PRODUCT, SALESTERRITORY
                                      200901, 10, 2, 3, 1
                                      200902, 11, 3, 3, 1
                                      201001, 14, 2, 4, 2
                                      ] ;


                                      Temp_Max_YYYYMM_Table:

                                      LOAD CUSTOMER,
                                      MAX(YYYYMM) as YYYYMM
                                      Resident Temp_Test_Table
                                      group by CUSTOMER
                                      ;

                                      Max_YYYYMM_Table:
                                      Load YYYYMM,
                                      CUSTOMER,
                                      SALESREP,
                                      SALESTERRITORY
                                      Resident Temp_Test_Table
                                      ;

                                      inner join

                                      LOAD YYYYMM,
                                      CUSTOMER
                                      Resident Temp_Max_YYYYMM_Table
                                      ;

                                      Final_Table:
                                      LOAD YYYYMM,
                                      CUSTOMER,
                                      PRODUCT
                                      Resident Temp_Test_Table
                                      ;

                                      left join

                                      LOAD
                                      CUSTOMER,
                                      SALESREP,
                                      SALESTERRITORY
                                      Resident Max_YYYYMM_Table
                                      ;

                                      Drop Tables Temp_Test_Table, Temp_Max_YYYYMM_Table, Max_YYYYMM_Table;