Row level to Column level when string matches

[CK_WAKE]

I have a QVD load that contains duplicate records because of an identifier. So, instead of having duplicate records, I want to create an identifier that is Yes or No for a specific string. As an example below, you can see the Secondary Key column contains extra strings _ RE and _NA, and these are duplicate records, so I want to have the result as a second table with a column indicator as the RE Identifier.

I appreciate your help on this.

Actual Table

Primary key	Seondary key	col3	col4	col5	col6	col7
ABC123	B479238	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES
ABC123	B479238_RE	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES
ABC1234	B130636	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES
ABC1234	B130636_RE	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES
ABC12345	B280476	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES

Expected table:

Primary key	Seondary key	col3	col4	col5	col6	col7	RE Identifier
ABC123	B479238	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	Yes
ABC1234	B130636	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	Yes
ABC12345	B280476	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	SOME VALUES	No

[marcus_sommer · Accepted solution]

Maybe something like this:

m: mapping load distinct [Primary key], 1 from Source where right([Secondary Key], 2) = 'RE';

t: load distinct *, pick(applymap('m', [Primary key], 2), 'Yes', No') as Identifier
   from Source where right([Secondary Key], 2) <> 'RE';

[CK_WAKE]

Thanks for the help.