Last Value of a column

[#1]

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

%XRateID	CUSIP	Procmonth	ClassDescription	Issuer	Ccy	=GBPRate_ME	=Sum(SettAmt*IMVal)
201009_USD	ABC	201009	CASH & CASH EQUIVALENTS	XYZ	USD	1.583454366	129881.1
201010_USD	ABC	201010	CASH & CASH EQUIVALENTS	XYZ	USD	1.58829808	648409.01
201011_USD	ABC	201011	CASH & CASH EQUIVALENTS	XYZ	USD	1.552001717	1110524.86
201012_USD	ABC	201012	CASH & CASH EQUIVALENTS	XYZ	USD	1.553000528	-954556.55
201101_USD	ABC	201101	CASH & CASH EQUIVALENTS	XYZ	USD	1.58630239	109677.54
201102_USD	ABC	201102	CASH & CASH EQUIVALENTS	XYZ	USD	1.619396693	3032916.41
201103_USD	ABC	201103	CASH & CASH EQUIVALENTS	XYZ	USD	1.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

%XRateID	CUSIP	Procmonth	ClassDescription	Issuer	Ccy	=GBPRate_ME	=Sum(SettAmt*IMVal)	Converted Amount @ 1.601096751
201009_USD	ABC	201009	CASH & CASH EQUIVALENTS	XYZ	USD	1.583454366	129881.1	207952.2072
201010_USD	ABC	201010	CASH & CASH EQUIVALENTS	XYZ	USD	1.58829808	648409.01	1038165.559
201011_USD	ABC	201011	CASH & CASH EQUIVALENTS	XYZ	USD	1.552001717	1110524.86	1778057.745
201012_USD	ABC	201012	CASH & CASH EQUIVALENTS	XYZ	USD	1.553000528	-954556.55	-1528337.391
201101_USD	ABC	201101	CASH & CASH EQUIVALENTS	XYZ	USD	1.58630239	109677.54	175604.353
201102_USD	ABC	201102	CASH & CASH EQUIVALENTS	XYZ	USD	1.619396693	3032916.41	4855992.61
201103_USD	ABC	201103	CASH & CASH EQUIVALENTS	XYZ	USD	1.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

[kaushiknsolanki]

Hi,

     Have a look at the attached document.

     Hope this will help you.

Regards,

Kaushik Solanki

[#3]

Thank you very much for trying Kaushik.

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

%XRateID	Ccy	ClassDescription	Procmonth	Issuer	CUSIP	=GBPRate_ME	sum(GBPRate_ME)	Variable1 * 10
							11.08355053	16.01097
201009_USD	USD	CASH & CASH EQUIVALENTS	201009	XYZ	ABC	1.583454366	1.583454366	16.010968
201010_USD	USD	CASH & CASH EQUIVALENTS	201010	XYZ	ABC	1.58829808	1.58829808	16.010968
201011_USD	USD	CASH & CASH EQUIVALENTS	201011	XYZ	ABC	1.552001717	1.552001717	16.010968
201012_USD	USD	CASH & CASH EQUIVALENTS	201012	XYZ	ABC	1.553000528	1.553000528	16.010968
201101_USD	USD	CASH & CASH EQUIVALENTS	201101	XYZ	ABC	1.58630239	1.58630239	16.010968
201102_USD	USD	CASH & CASH EQUIVALENTS	201102	XYZ	ABC	1.619396693	1.619396693	16.010968
201103_USD	USD	CASH & CASH EQUIVALENTS	201103	XYZ	ABC	1.601096751	1.601096751	16.010968
201009_CAD	CAD	CASH & CASH EQUIVALENTS	201009	XYZ	ABC	1.49
201010_CAD	CAD	CASH & CASH EQUIVALENTS	201010	XYZ	ABC	1.54
201011_CAD	CAD	CASH & CASH EQUIVALENTS	201011	XYZ	ABC	1.84
201012_CAD	CAD	CASH & CASH EQUIVALENTS	201012	XYZ	ABC	1.553000528
201101_CAD	CAD	CASH & CASH EQUIVALENTS	201101	XYZ	ABC	1.58630239
201102_CAD	CAD	CASH & CASH EQUIVALENTS	201102	XYZ	ABC	1.619396693
201103_CAD	CAD	CASH & CASH EQUIVALENTS	201103	XYZ	ABC	1.8925

I apologise i should have given the complete problem

Thank you very much for your time.I appreciate it

Cheers,

Sumit

[kaushiknsolanki · Accepted solution]

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

[#5]

Thanks Kaushik!!

You gave me enough clue to work around this problem.

Cheers,

Sumit

[kaushiknsolanki]

Hi,

    Thats gr8.

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

Regards,

Kaushik Solanki

[Miguel_Angel_Baeyens]

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