Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to match ACCOUNT NO to a POLICY NO, if it matches, in this example account no 100000 matches policy no 100000 so the account type is AAAA (That's easy). The hard bit, i need to create another column and populate all the other Policy no for Account no 100000 with AAAA.
OLD
ACCOUNT NO | POLICY NO | ACCOUNT TYPE |
100000 | 100000 | AAAA |
100000 | 100001 | PASI |
100000 | 100002 | LASI |
100000 | 100003 | PAAA |
100000 | 100004 | KAAA |
100000 | 100005 | IUAJ |
100000 | 100006 | ASSA |
627811 | 627811 | BBBB |
627811 | 627812 | QAWS |
627811 | 627813 | HDGD |
627811 | 627814 | SHAF |
627811 | 627815 | AJSS |
783793 | 783793 | LSD |
783793 | 783794 | SJS |
783793 | 783795 | HJS |
783793 | 783796 | AHA |
783793 | 783797 | OOO |
REQUIRED
ACCOUNT NO | POLICY NO | ACCOUNT TYPE | NEW COLUMN |
100000 | 100000 | AAAA | AAAA |
100000 | 100001 | PASI | AAAA |
100000 | 100002 | LASI | AAAA |
100000 | 100003 | PAAA | AAAA |
100000 | 100004 | KAAA | AAAA |
100000 | 100005 | IUAJ | AAAA |
100000 | 100006 | ASSA | AAAA |
627811 | 627811 | BBBB | BBBB |
627811 | 627812 | QAWS | BBBB |
627811 | 627813 | HDGD | BBBB |
627811 | 627814 | SHAF | BBBB |
627811 | 627815 | AJSS | BBBB |
783793 | 783793 | LSD | LSD |
783793 | 783794 | SJS | LSD |
783793 | 783795 | HJS | LSD |
783793 | 783796 | AHA | LSD |
783793 | 783797 | OOO | LSD |
Thanks guys.
I would go for a Mapping approach:
MAP:
MAPPING LOAD [Account No], [Account Type] FROM OLD WHERE [Account No] = [Policy No];
REQUIRED;
LOAD *,
ApplyMap('MAP',[Account No],'Mapping not found') as [NEW COLUMN]
FROM OLD;
I would go for a Mapping approach:
MAP:
MAPPING LOAD [Account No], [Account Type] FROM OLD WHERE [Account No] = [Policy No];
REQUIRED;
LOAD *,
ApplyMap('MAP',[Account No],'Mapping not found') as [NEW COLUMN]
FROM OLD;
Thats fantasic mate, Thanks.
Another way is
Load
ACCOUNT NO,
POLICY NO,
ACCOUNT TYPE,
If(ACCOUNT NO = POLICY NO, ACCOUNT TYPE,'Not Found' ) as NEW COLUMN
From Location;
I was nearly there, i was using the wrong fields in the Mapping.
This does'nt work, the AAAA only populates for the matched row, i need to to show in all the fields in the new column for that account number.
I've been looking at the data and the conditions met are not 100%. There's a case of no mathes in the account number and policy number, but the new column still populates rather than saying no match.
Could you upload a small sample application or your source table that demonstrates this issue?
Let me look into it my end, as the code works fine with my Excel example, maybe there's something in my data that's wrong.
I can't upload the real data
What is the value that is populating these fields? Account type or Account No?
Does the default string works for other Account No with no match?