topic Re: Using INDEX( MATCH( formula in a dimension in QlikView

Using INDEX( MATCH( formula in a dimension

Wed, 11 May 2016 16:08:26 GMT

Hi all,

I have the following formula in excel, that I want to recreate in QlikView.

=IF(P2="Net Settlement",INDEX(A:P,MATCH(1,(D:D=D2)*(I:I=I2)*(P:P="Principal"),0),14),N2)

(Column AB)

The idea is to change all the books where Type = "Net Settlement" to the Type = "Principal", where the Long Name and SEDOL

is the same.

Please assist, as it is very tricky.

Thank you

Re: Using INDEX( MATCH( formula in a dimension

petter — Thu, 12 May 2016 00:23:51 GMT

I think it is easier for you to get help if you explain what this formula actually does ... in English. I looked at the spreadsheet and it still seemed quite cryptic to me what it does in Excel...

Re: Using INDEX( MATCH( formula in a dimension

sunny_talwar — Thu, 12 May 2016 01:42:20 GMT

May be this:

Table:

LOAD Status,

Date,

[Value Date],

[Long Name],

Reference,

Operation,

[I Long Name],

Reference1,

SEDOL,

Ccy,

[Quantity (+/-)],

[Amount (+/-)],

Price,

Book,

Market,

Type,

Code,

Commission,

Consideration,

Salesperson,

[Short Name],

Terms,

[PTM Base Amount],

Code1,

[Process Lite by Security / CCP Netting Eligible],

[Company Depot Alias],

[Client/Broker],

// [Net Books],

Sector

FROM

[NetSettlement Depot.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join (Table)

LOAD DISTINCT

[Long Name],

SEDOL,

Book as Temp

Resident Table

Where Type = 'Principal';

FinalTable:

LOAD *,

If(Type = 'Net Settlement', Temp, Book) as New_Books

Resident Table;

DROP Table Table;

Re: Using INDEX( MATCH( formula in a dimension

Thu, 12 May 2016 09:15:51 GMT

Thank you Sunny

I will try and get back to you

Re: Using INDEX( MATCH( formula in a dimension

Fri, 20 May 2016 08:17:26 GMT

Apologies for the late reply Sunny . Thank you very much for your response.

I have updated the script in my current QlikView dashboard, also returning the data in the inner join.

The outputs are below :

From QlikView:

Actual (pivot on raw data):

Counterparty Long Name	SEDOL	Trade Type	Book	New Books	Total
CREDIT SUISSE SECS	B1XFTL2	Net Settlement	NETP	INFR1	1
		Principal	INFR1	INFR1	1
			INFRAS OMAR	INFRAS OMAR	1

When comparing the two you can see that the Principal Trade are duplicated. Do you know why that is?

This appears when we have more than one Book for a Principal and a Net Settlement for the same Counterparty.

Thank you very much.

Mary