2 Replies Latest reply: Apr 16, 2013 3:28 AM by vishal waghole RSS

    Evaluate where.....

    Adotey Kwame

      Hi,

       

      I need help with the evaluate SUM(Amount * Current_rate) , where Table A ID = Table B ID and Table A Doc = Table B Doc.

       

       

        Table A:                                             Table B:

      Current_Rate,                                      Amount,

      Branch_code,                                        Doc,

      ID ,                                                        ID     

      Doc

       

      FROM XXX                                          FROM YYY

        • Re: Evaluate where.....
          Sandro Pividori

          Hi.

          In the script you could add the following lines:

           

          left join(Table A)

          load ID, Doc, Amount

          resident Table B;

           

          left join(Table A)

          load ID, Doc, Amount*Current_Rate as Result

          resident Table A

           

          Hope this helps.

           

          Regards.

          • Re: Evaluate where.....
            vishal waghole

            Hi,

             

            you could make composite key on id and doc field.

             

            Like this,

             

            Table A:

            Load     Current_Rate,

                        Branch_code,

                        Doc,

                        ID,                                              

                        ID &'-'& Doc as Key1

            from    XXX;

             

            left join(Table A)

            Load    ID &'-'& Doc as Key1

                      Amount

            from    YYY;

             

            FinalTable:

            Load    *,

                       sum(Amount * Current_rate)

            Resident Table A;

             

            Drop table Table A;

             

            Hope this will work fine for you.

             

            Thanks and Regards,

            Vishal Waghole.