topic Rows getting duplicated when conditional columns are added in QlikView

Rows getting duplicated when conditional columns are added

rsdhavle — Fri, 23 Mar 2018 12:51:27 GMT

I am joining two tables on one key field. I have to calculate few conditional columns based on values from one column of a second table.

When I add these conditional columns along with columns present in first table inside straight table rows with null entries get added in the table. How to handle this?

Re: Rows getting duplicated when conditional columns added

YoussefBelloum — Fri, 23 Mar 2018 12:56:51 GMT

Hi,

I think we need more details here. would you be able to share some data and the script ? or to attach some screen shots ?

Re: Rows getting duplicated when conditional columns added

rsdhavle — Sun, 25 Mar 2018 08:19:08 GMT

Table1

Client Unique Records	ClientType	Country	Region
A	X
B	Y
C	z

Table 2

Client	Role	RoleName	EmpName
A	1	SalesHead	ABCD
A	2	RegionHead	XYZ
B	3	SalesManager	UVW

Now by joining Table 2 with 1 on key client I need to form additional columns with names as Sales Head , Region Head which should display employee names from table 2 as their values so resulting table will be like below.

Currently I am doing left join for each role id with table 1 as in doing self join but would like to know if there is better solution to handle this scenario like applymap or anything else..

ClientUniqueRecond	clienttype	SalesHead	RegionHead
A	X	ABCD	XYZ

Re: Rows getting duplicated when conditional columns are added

rsdhavle — Wed, 28 Mar 2018 06:46:04 GMT

gwassenaar‌ can you suggest a good solution for this?

Re: Rows getting duplicated when conditional columns are added

PrashantSangle — Wed, 28 Mar 2018 07:56:15 GMT

Try like

Load * from Table1;

Left Join

Load Client,Role,EmpName as SalesHead from Table2

where RoleName='SalesHead';

Left Join

Load Client,Role,EmpName as RegionHead from Table2

where RoleName='RegionHead';

Left Join

Load Client,Role,EmpName as SalesManager from Table2

where RoleName='SalesManager';

Regards

Re: Rows getting duplicated when conditional columns are added

YoussefBelloum — Wed, 28 Mar 2018 07:56:55 GMT

What is the calculation rule for the new field "region head" here ?

for Client A, it could be XYZ or ABCD, right ?

Re: Rows getting duplicated when conditional columns are added

PrashantSangle — Wed, 28 Mar 2018 08:02:10 GMT

I think it is based on RoleName

Regards,

Re: Rows getting duplicated when conditional columns are added

YoussefBelloum — Wed, 28 Mar 2018 08:13:28 GMT

Yes, it is the only logical explanation..

Re: Rows getting duplicated when conditional columns are added

rsdhavle — Wed, 28 Mar 2018 12:36:16 GMT

Yes I have implemented it like this way currently. I would like to know if we use some function like ApplyMap and create multiple Mapping loads will that be a better solution performance wise or it will not make much difference?