Question Re: Deleting rows based on other rows - Data Action in Technology Q&A

Deleting rows based on other rows - Data Action

andreag412 — Mon, 01 Sep 2025 16:45:08 GMT

Dear Community,

Option 1

We start from a situation like the one shown in the table below.

ACCOUNT

VIEW

ENTITY

DATASOURCE

CUST

MARK

INT

Amount

ACCOUNT1

V00R

CX20

INP

STK

DEL

RET

FTB998

CNY

COMM

1000

ACCOUNT1

V00R

CX20

ADD

STK

DEL

RET

CNY

FTB998

CNY

COMM

1000

ACCOUNT1

V00R

CX20

ADD

STK

DEL

RET

EUR

FTB998

CNY

COMM

1000

ACCOUNT1

V00R

CX20

ADD

STK

DEL

RET

IDR

FTB998

CNY

COMM

1000

ACCOUNT1

V00R

CX20

ADD

STK

DEL

RET

INR

FTB998

CNY

COMM

1000

ACCOUNT1

V00R

CX20

ADD

STK

DEL

RET

JPY

FTB998

CNY

COMM

1000

ACCOUNT1

V00R

CX20

ADD

STK

DEL

RET

SGD

FTB998

CNY

COMM

1000

ACCOUNT1

V00R

CX20

ADD

STK

DEL

RET

USD

FTB998

CNY

COMM

1000

ACCOUNT1

V00R

CX20

ADD

STK

DEL

RET

VND

FTB998

CNY

COMM

1000

ACCOUNT1

CX20

INP

STK

CNY

FTB998

1000

The requirement is to selectively delete the ADD rows; the only rows that should remain are those with equal PR and CC values (as in the final table).

ACCOUNT

VIEW

ENTITY

DATASOURCE

CUST

MARK

INT

Amount

ACCOUNT1

V00R

CX20

ADD

STK

DEL

RET

CNY

FTB998

CNY

COMM

1000

ACCOUNT1

CX20

INP

STK

CNY

FTB998

1000

Option 2
We start from a situation such as that shown in the table

ACCOUNT

VIEW

ENTITY

DATASOURCE

CUST

MARK

INT

Amount

ACCOUNT1

V00R

CX20

INP

STK

DEL

RET

FTB998

CNY

COMM

1000

ACCOUNT1

CX20

INP

STK

CNY

FTB998

1000

Here, the requirement is to report where CC = # is present, the same CC present in the row where the following dimensions are the same: ENTITY, DATASOURCE, DF, PR.

In this case, the result would be as follows

ACCOUNT

VIEW

ENTITY

DATASOURCE

CUST

MARK

INT

Amount

ACCOUNT1

V00R

CX20

INP

STK

DEL

RET

CNY

FTB998

CNY

COMM

1000

ACCOUNT1

CX20

INP

STK

CNY

FTB998

1000

Would either option work? What Data Action (Advanced Formula) needs to be created to achieve this result?
Obviously, these are sample data; in the data set there are more PRs and different combinations.

Thank you

Re: Deleting rows based on other rows - Data Action

N1kh1l — Tue, 02 Sep 2025 05:21:26 GMT

@andreag412 For Option 1 , I think you want to delete the data where currency is not the same as Local currency of the market. In your sample data its CNY for Market CN. If your market dimension has an attribute for currency with value CN, the below sample Data Action logic can work. For both Option 1 and 2 Assumption is Currency attribute in MARK is set to CNY

MEMBERSET [d/DATASOURCE]="ADD" IF [d/MARK].[p/Currency] != [d/CC].[p/id] THEN DELETE() ENDIF

For Option 2

MEMBERSET [d/DATASOURCE]="INP" MEMBERSET [d/CC]="#" DATA([d/CC]=[d/MARK].[p/Currency])=RSEULTLOOKUP([d/CC]="#")

Hope this helps !!

Nikhil

Re: Deleting rows based on other rows - Data Action

andreag412 — Tue, 02 Sep 2025 07:50:30 GMT

Hi,

No, unfortunately currency is not a market attribute, but the CC dimension is independent. In this example, it is a case that market and currency coincide.

Re: Deleting rows based on other rows - Data Action

N1kh1l — Tue, 02 Sep 2025 08:06:38 GMT

In that case what is the logic which decides which currency results you want to delete ? In this case you are only keeping CNY for CN and deleting all others.

Re: Deleting rows based on other rows - Data Action

andreag412 — Tue, 02 Sep 2025 08:13:36 GMT

The logic is that I need to check the CC and PR dimensions using the row where VIEW and MARK= # and keep all those rows where DATASOURCE = ADD and PR and CC are equal to that row.