ApplyMap function for multiple fields

[Anonymous]

Hi everyone,

I have a somewhat complicated dataset and just can't seem to get the mappings to cooperate. For context, I have 3 layers: Layer 1 which can either be Client or Not_Client; Layer 2 which can range from "Strong sell", "Sell", "Hold", "Buy", "Strong Buy" respectively; and Layer 3 which represents a list of Investments (72 of them).

So basically, each investment needs to be mapped to a classification strong sell to strong buy, and this needs to be done for both Client and Non_Client fields. I've managed to use the ApplyMap function to create the appropriate mappings but the problem arises when I pull in numeric values for each layer. I.e. I have returns for each investment at each layer.

I want to be able to click on client and see an aggregated return, then click on the next layer (say Buy) and see the aggregated Buy return as well as the associated investments and then finally click on an investment then see its return. I have attached a spreadsheet of the mapping table i used and the format of the database which stores the returns.

Just to clarify I have been able to create the associations in the mapping tab with the applymap function. What doesn't happen however, is the appropriate associations of the returns from the database. The applymap just maps the first appearance of the Investments (i.e. with the Client field) and doesn't remap it for the next appearance (in the Not_Client field). If this isn't clear, happy to provide more info. Any help would be appreciated.

[sunny_talwar]

May be join instead of using ApplyMap

[Anonymous]

Tried that as well. Similar problem!

[sunny_talwar]

Would you be able to share the script you have tried? Also, it would be good to know what the final table needs to look like (for your sample)

[Anonymous]

//Loading mappings:

Mapping1:

Mapping Load

Layer3,

Layer2

FROM

(ooxml, embedded labels);

Mapping2:

Mapping Load

Layer3,

Layer1

FROM

(ooxml, embedded labels);

//Loading returns data:

Table:

Load

Date,

Name,

Return,

Type

From...

Table1:

Load Date,

Name,

Return,

Type,

if(Type = 'Layer3', Name, NULL()) as Layer3,

ApplyMap('Mapping1',Name,NULL()) as Layer2,

ApplyMap('Mapping2',Name, NULL()) as Layer1

Resident Table;

Layer1:

NoConcatenate Load

Return as Layer1Return,

Date,

Name as Layer1,

Type

Resident Table where Type='Layer1';

Join(Table1)

Layer1Return,

Date,

Layer1,

Type

Resident(Layer1);

Drop table Layer1;

Layer2:

NoConcatenate Load

Return as Layer2Return,

Date,

Name as Layer2,

Type

Resident Table where Type='Layer2';

Join(Table1)

Layer2Return,

Date,

Layer2,

Type

Resident(Layer2);

Drop table Layer2;

Layer3:

NoConcatenate Load

Return as Layer3Return,

Date,

Name as Layer3,

Type

Resident Table where Type='Layer3';

Join(Table1)

Layer3Return,

Date,

Layer3,

Type

Resident(Layer3);

Drop table Layer3;

Drop table Table;

[Anonymous]

This is more or less the script that got me the closest to the desired outcome. I don't mind what the final table looks like as long as the correct relationships are achieved. I.e. at the first layer, the associated returns and further layers are correct