Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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';
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';
Thanks for the help.