Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Take data from one column and populate in another column

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 NOPOLICY NOACCOUNT  TYPE
100000100000AAAA
100000100001PASI
100000100002LASI
100000100003PAAA
100000100004KAAA
100000100005IUAJ
100000100006ASSA
627811627811BBBB
627811627812QAWS
627811627813HDGD
627811627814SHAF
627811627815AJSS
783793783793LSD
783793783794SJS
783793783795HJS
783793783796AHA
783793783797OOO

REQUIRED

ACCOUNT NOPOLICY NOACCOUNT  TYPENEW COLUMN
100000100000AAAAAAAA
100000100001PASIAAAA
100000100002LASIAAAA
100000100003PAAAAAAA
100000100004KAAAAAAA
100000100005IUAJAAAA
100000100006ASSAAAAA
627811627811BBBBBBBB
627811627812QAWSBBBB
627811627813HDGDBBBB
627811627814SHAFBBBB
627811627815AJSSBBBB
783793783793LSDLSD
783793783794SJSLSD
783793783795HJSLSD
783793783796AHALSD
783793783797OOOLSD

Thanks guys.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

9 Replies
swuehl
MVP
MVP

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;

Anonymous
Not applicable
Author

Thats fantasic mate, Thanks.

its_anandrjs

Another way is

Load

ACCOUNT NO,

POLICY NO,

ACCOUNT TYPE,

If(ACCOUNT NO = POLICY NO, ACCOUNT TYPE,'Not Found' ) as NEW COLUMN

From Location;

Anonymous
Not applicable
Author

I was nearly there, i was using the wrong fields in the Mapping.

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

swuehl
MVP
MVP

Could you upload a small sample application or your source table that demonstrates this issue?

Anonymous
Not applicable
Author

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

swuehl
MVP
MVP

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?