Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

lakwinder
Contributor III

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
MVP
MVP

Re: Take data from one column and populate in another column

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;

9 Replies
MVP
MVP

Re: Take data from one column and populate in another column

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;

lakwinder
Contributor III

Re: Take data from one column and populate in another column

Thats fantasic mate, Thanks.

Re: Take data from one column and populate in another column

Another way is

Load

ACCOUNT NO,

POLICY NO,

ACCOUNT TYPE,

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

From Location;

lakwinder
Contributor III

Re: Take data from one column and populate in another column

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

lakwinder
Contributor III

Re: Take data from one column and populate in another column

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.

lakwinder
Contributor III

Re: Take data from one column and populate in another column

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.

MVP
MVP

Re: Take data from one column and populate in another column

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

lakwinder
Contributor III

Re: Take data from one column and populate in another column

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

MVP
MVP

Re: Take data from one column and populate in another column

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?

Community Browser