6 Replies Latest reply: Jun 21, 2011 5:55 AM by Miguel Angel Baeyens de Arce RSS

    Last Value of a column

      Dear Qlikview Gurus,

       

      I am struggling with an issue and i thought it might be a good idea to get your point of view on this.

      Following is the problem

       

      %XRateIDCUSIPProcmonthClassDescriptionIssuerCcy=GBPRate_ME=Sum(SettAmt*IMVal)
      201009_USDABC201009CASH & CASH EQUIVALENTSXYZUSD1.583454366129881.1
      201010_USDABC201010CASH & CASH EQUIVALENTSXYZUSD1.58829808648409.01
      201011_USDABC201011CASH & CASH EQUIVALENTSXYZUSD1.5520017171110524.86
      201012_USDABC201012CASH & CASH EQUIVALENTSXYZUSD1.553000528-954556.55
      201101_USDABC201101CASH & CASH EQUIVALENTSXYZUSD1.58630239109677.54
      201102_USDABC201102CASH & CASH EQUIVALENTSXYZUSD1.6193966933032916.41
      201103_USDABC201103CASH & CASH EQUIVALENTSXYZUSD1.601096751-1496620.16


      The above table is chart type

      The last column "Sum(SettAmt*IMVal)" is an expression.

      What i want to do is get and equivalent GBP amount for Sum(SettAmt*IMVal) column using the rate of exchange mentioned in "GBPRate_ME" column.

      But the challenge is i need to use the latest rate of exchange for this conversion i.e the Rate of exchange associated with 2011_03_USD row(%XRateID) i.e 1.601096751 for all the conversions. I have highligted this value in RED.

       

      So that resulted grid looks like this

       

      %XRateIDCUSIPProcmonthClassDescriptionIssuerCcy=GBPRate_ME=Sum(SettAmt*IMVal)Converted Amount @ 1.601096751
      201009_USDABC201009CASH & CASH EQUIVALENTSXYZUSD1.583454366129881.1207952.2072
      201010_USDABC201010CASH & CASH EQUIVALENTSXYZUSD1.58829808648409.011038165.559
      201011_USDABC201011CASH & CASH EQUIVALENTSXYZUSD1.5520017171110524.861778057.745
      201012_USDABC201012CASH & CASH EQUIVALENTSXYZUSD1.553000528-954556.55-1528337.391
      201101_USDABC201101CASH & CASH EQUIVALENTSXYZUSD1.58630239109677.54175604.353
      201102_USDABC201102CASH & CASH EQUIVALENTSXYZUSD1.6193966933032916.414855992.61
      201103_USDABC201103CASH & CASH EQUIVALENTSXYZUSD1.601096751-1496620.16-2396233.676

       

      Please let me know if the problem statement is not clear

      I am grateful for your time on this.

       

      Thanks

       

      Regards,

      Sumit

        • Re: Last Value of a column
          Kaushik Solanki

          Hi,

           

               Have a look at the attached document.

           

               Hope this will help you.

           

          Regards,

          Kaushik Solanki

            • Last Value of a column

              Thank you very much for trying Kaushik.

              Will this work when there are different currencies as well. for ex:

               

              %XRateIDCcyClassDescriptionProcmonthIssuerCUSIP=GBPRate_MEsum(GBPRate_ME)Variable1 * 10
              11.0835505316.01097
              201009_USDUSDCASH & CASH EQUIVALENTS201009XYZABC1.5834543661.58345436616.010968
              201010_USDUSDCASH & CASH EQUIVALENTS201010XYZABC1.588298081.5882980816.010968
              201011_USDUSDCASH & CASH EQUIVALENTS201011XYZABC1.5520017171.55200171716.010968
              201012_USDUSDCASH & CASH EQUIVALENTS201012XYZABC1.5530005281.55300052816.010968
              201101_USDUSDCASH & CASH EQUIVALENTS201101XYZABC1.586302391.5863023916.010968
              201102_USDUSDCASH & CASH EQUIVALENTS201102XYZABC1.6193966931.61939669316.010968
              201103_USDUSDCASH & CASH EQUIVALENTS201103XYZABC1.6010967511.60109675116.010968
              201009_CADCADCASH & CASH EQUIVALENTS201009XYZABC1.49
              201010_CADCADCASH & CASH EQUIVALENTS201010XYZABC1.54
              201011_CADCADCASH & CASH EQUIVALENTS201011XYZABC1.84
              201012_CADCADCASH & CASH EQUIVALENTS201012XYZABC1.553000528
              201101_CADCADCASH & CASH EQUIVALENTS201101XYZABC1.58630239
              201102_CADCADCASH & CASH EQUIVALENTS201102XYZABC1.619396693
              201103_CADCADCASH & CASH EQUIVALENTS201103XYZABC1.8925

              I apologise i should have given the complete problem

               

              Thank you very much for your time.I appreciate it

               

              Cheers,

              Sumit