topic How to pick required data if certain fields are repeated in QlikView

How to pick required data if certain fields are repeated

pra_kale — Wed, 14 Jun 2017 13:16:41 GMT

Hi,

I have a dataset in which policies are repeated because of remark. Below given is the e.g.

Policy_No	Name	Remark
123456	Rajesh	Yes
23456	Nitesh	No
123456	Rajesh	No
56789	Jiten	Yes
123456	Rajesh	No
23456	Nitesh	Yes
366999	Jiire	No

Requirement is where ever policies are repeated and if Remark is Yes then that policy should get selected and for rest policies which are not repeated should get picked up as is.

Final out-put should be like given below.

Policy_No	Name	Remark
23456	Nitesh	Yes
56789	Jiten	Yes
123456	Rajesh	Yes
366999	Jiire	No

Please help to get the desired out-put.

Thanks in Advance.

Re: How to pick required data if certain fields are repeated

sunny_talwar — Wed, 14 Jun 2017 13:21:07 GMT

May be this

Table:

LOAD * INLINE [

Policy_No, Name, Remark

123456, Rajesh, Yes

23456, Nitesh, No

123456, Rajesh, No

56789, Jiten, Yes

123456, Rajesh, No

23456, Nitesh, Yes

366999, Jiire, No

];

Right Join (Table)

LOAD Policy_No,

MaxString(Remark) as Remark

Resident Table

Group By Policy_No;

Re: How to pick required data if certain fields are repeated

thomaslg_wq — Wed, 14 Jun 2017 15:12:31 GMT

Or this

Table:

LOAD DISTINCT

Policy_No,

Name,

Remark

from Source

where Remark='yes';

concatenate(Table)

LOAD DISTINCT

Policy_No,

Name,

Remark

from Source

where not exists(Policy_No,Policy_No);

Re: How to pick required data if certain fields are repeated

pra_kale — Wed, 14 Jun 2017 15:55:24 GMT

Thanks Sunny It is working .

Re: How to pick required data if certain fields are repeated

pra_kale — Wed, 14 Jun 2017 15:55:58 GMT

Thanks Thomas...This solution is also working.