4 Replies Latest reply: Jan 16, 2017 12:04 AM by Praveen Prithiviraj RSS

    Last Month Values

    alvinford ford

      Hi Everyone,

       

      I have requirement in currency conversion. I have data (Amount.xls) and currency conversion (Conversion Rate.xls). Have to use the previous  month Exchange Rate values for the current month amount. Want to do it at script level . How do I achieve it.

       

      Your help is appreciated. Please refer the attached excel.

       

      Thanks & Regards,

      Alvin

        • Re: Last Month Values
          Muñoz Héctor

          Hi alvinford,

           

          Please try the following script code:

           

          // Mapping table with combinations of dates and exchange rates

          MAP_ER:

          MAPPING LOAD Date#(Date, 'YYYYMM') AS [MAP_ER Date],

                       RATE                  AS [MAP_ER Rate]

          FROM         [Exchange Rate.xls] (biff, embedded labels, table is Sheet1$);

           

           

          // Fact table with date, amount, exchange rate (taken from previous mapping table) and amount * exchange rate fields

          AMOUNTS:

          LOAD *,

               Amount * [Exchange Rate] AS [Amount ER];

          LOAD Date                                                        AS Date,

               Amount                                                      AS Amount,

               ApplyMap('MAP_ER', AddMonths(Date#(Date, 'YYYYMM'), -1), 0) AS [Exchange Rate]

          FROM Amount.xls (biff, embedded labels, table is Sheet1$);

           

          Notice than source files are in the same folder as than the QV document.

           

          Regards from Spain,
          Héctor

          • Re: Last Month Values
            Rahul Pawar

            Hello Alvin,

             

            Trust you are good.

             

            Please refer below code snipped to resolve your queries.

            //Load Amount table
            Amount:
            LOAD Date, 
                 Amount
            FROM
            Amount.xls
            (biff, embedded labels, table is Sheet1$)
            Where Len(Date) > 0;
            
            
            //Load Exchange Rate values
            //Rename Date field because we would like to join Amount table with ExchangeRate table 
            //based on prior month of the date present in Amount table
            //Create new field from Date field to show prior month of the date present in Amount table
            ExchangeRate:
            LOAD Date AS ExchangeRateDate,  
                 If(Right(Date,2) = 01, Date -89, Date -1) AS Date, 
                 If(Len(RATE)>0, RATE, 1) AS RATE
            FROM
            [Exchange Rate.xls]
            (biff, embedded labels, table is Sheet1$)
            Where Len(RATE) > 0;
            

             

            At Expression Level you can use below:

            Sum(Amount * If(Len(RATE)>0, RATE, 1))

             

            Let me know if you have further queries.

             

            Thank you!

            Rahul

            • Re: Last Month Values
              Sunny Talwar

              You are sharing already aggregated data which is probably not the best way to do things, but having said that logic can remain the same.

               

              AmtTable:

              LOAD Date(Date#(Date,  'YYYYMM'), 'YYYYMM') as Date,

                  Amount

              FROM

              [..\..\..\Downloads\Amount.xls]

              (biff, embedded labels, table is Sheet1$)

              Where Len(Trim(Date)) > 0;

               

              Left Join(AmtTable)

              LOAD Date(AddMonths(Date#(Date,  'YYYYMM'), 1), 'YYYYMM') as Date,

                  RATE

              FROM

              [..\..\..\Downloads\Exchange Rate.xls]

              (biff, embedded labels, table is Sheet1$)

              Where Len(Trim(RATE)) > 0;

               

              In the above script, I am Left Joining the Exchange Rates into AmtTable, but you can keep it as a separate table (and use Left Keep)

               

              Capture.PNG

              • Re: Last Month Values
                Praveen Prithiviraj

                Try this...

                 

                tab1:

                LOAD Date,

                     RATE    

                FROM

                [Exchange Rate.xls]

                (biff, embedded labels, table is Sheet1$);

                 

                tab2:

                LOAD Date,

                     Amount,

                     Lookup('RATE','Date', [Date], 'tab1') as ExRate

                FROM

                [Amount.xls]

                (biff, embedded labels, table is Sheet1$);

                STORE tab2 into main.qvd(qvd);

                DROP Table tab1;

                DROP Table tab2;

                 

                tab3:

                LOAD Date,

                     Amount,

                     ExRate    

                from main.qvd(qvd);