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

Take data from one column and populate in another column

Anonymous — Fri, 30 May 2014 12:10:12 GMT

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.

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

swuehl — Fri, 30 May 2014 12:15:43 GMT

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;

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

Anonymous — Fri, 30 May 2014 12:26:38 GMT

Thats fantasic mate, Thanks.

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

its_anandrjs — Fri, 30 May 2014 12:39:39 GMT

Another way is

Load

ACCOUNT NO,

POLICY NO,

ACCOUNT TYPE,

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

From Location;

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

Anonymous — Fri, 30 May 2014 13:31:50 GMT

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

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

Anonymous — Fri, 30 May 2014 14:57:44 GMT

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.

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

Anonymous — Fri, 30 May 2014 14:59:23 GMT

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.

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

swuehl — Fri, 30 May 2014 15:01:58 GMT

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

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

Anonymous — Fri, 30 May 2014 15:06:10 GMT

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

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

swuehl — Fri, 30 May 2014 15:10:17 GMT

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?