Community
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

Deleting rows based on other rows - Data Action

andreag412
Explorer

on ‎2025 Sep 01 5:45 PM

0 Likes
765

Dear Community,

Option 1 

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

ACCOUNTVIEWENTITYDATASOURCEDFCUSTCHCCMARKPRTCSCINTAmount
ACCOUNT1V00RCX20INPSTKDELRET#CNFTB998CNYCOMM#1000
ACCOUNT1V00RCX20ADDSTKDELRETCNYCNFTB998CNYCOMM#1000
ACCOUNT1V00RCX20ADDSTKDELRETEURCNFTB998CNYCOMM#1000
ACCOUNT1V00RCX20ADDSTKDELRETIDRCNFTB998CNYCOMM#1000
ACCOUNT1V00RCX20ADDSTKDELRETINRCNFTB998CNYCOMM#1000
ACCOUNT1V00RCX20ADDSTKDELRETJPYCNFTB998CNYCOMM#1000
ACCOUNT1V00RCX20ADDSTKDELRETSGDCNFTB998CNYCOMM#1000
ACCOUNT1V00RCX20ADDSTKDELRETUSDCNFTB998CNYCOMM#1000
ACCOUNT1V00RCX20ADDSTKDELRETVNDCNFTB998CNYCOMM#1000
ACCOUNT1#CX20INPSTK##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).

ACCOUNTVIEWENTITYDATASOURCEDFCUSTCHCCMARKPRTCSCINTAmount
ACCOUNT1V00RCX20ADDSTKDELRETCNYCNFTB998CNYCOMM#1000
ACCOUNT1#CX20INPSTK##CNY#FTB998###1000

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

ACCOUNTVIEWENTITYDATASOURCEDFCUSTCHCCMARKPRTCSCINTAmount
ACCOUNT1V00RCX20INPSTKDELRET#CNFTB998CNYCOMM#1000
ACCOUNT1#CX20INPSTK##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

ACCOUNTVIEWENTITYDATASOURCEDFCUSTCHCCMARKPRTCSCINTAmount
ACCOUNT1V00RCX20INPSTKDELRETCNYCNFTB998CNYCOMM#1000
ACCOUNT1#CX20INPSTK##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

Know the answer?

Help others by sharing your knowledge.

Answer

Need more details?

Request clarification before answering.

Show replies
Show replies

Accepted Solutions (0)

Answers (1)

Answers (1)

N1kh1l
Active Contributor
‎2025 Sep 02 6:21 AM
0 Likes

@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

Show replies
Show replies
andreag412
Explorer
‎2025 Sep 02 8:50 AM
0 Likes

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.

N1kh1l
Active Contributor
‎2025 Sep 02 9:06 AM
0 Likes
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.
andreag412
Explorer
‎2025 Sep 02 9:13 AM
0 Likes
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.
Ask a Question