Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

1 Solution

Accepted Solutions
kaushik_solanki
Not applicable

Re: Last Value of a column

HI,

    Yes

 

    In that case you have to include a set analysis in max.

    max({Ccy = {"CAD"}}GBPrate_ME)

    Hope this will solve your problem.

Regards,

Kaushik Solanki

6 Replies
kaushik_solanki
Not applicable

Re: Last Value of a column

Hi,

     Have a look at the attached document.

     Hope this will help you.

Regards,

Kaushik Solanki

Not applicable

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

kaushik_solanki
Not applicable

Re: Last Value of a column

HI,

    Yes

 

    In that case you have to include a set analysis in max.

    max({Ccy = {"CAD"}}GBPrate_ME)

    Hope this will solve your problem.

Regards,

Kaushik Solanki

Not applicable

Last Value of a column

Thanks Kaushik!!

You gave me enough clue to work around this problem.

Cheers,

Sumit

kaushik_solanki
Not applicable

Re: Last Value of a column

Hi,

    Thats gr8.

    if you got the answer can you please mark this post as answered.

Regards,

Kaushik Solanki

Miguel_Angel_Baeyens
Not applicable

Re: Last Value of a column

Hi,

For what it's worth and to help further readers to get the proper result, the syntax shoud be

max({< Ccy = {"CAD"} >} GBPrate_ME)

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica