Using INDEX( MATCH( formula in a dimension

[#1]

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

[petter]

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...

[sunny_talwar]

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;

[#4]

Thank you Sunny

I will try and get back to you

[#5]

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