Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

How to pick required data if certain fields are repeated

Hi,

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

                          

Policy_NoNameRemark
123456RajeshYes
23456NiteshNo
123456RajeshNo
56789JitenYes
123456RajeshNo
23456NiteshYes
366999JiireNo

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_NoNameRemark
23456NiteshYes
56789JitenYes
123456RajeshYes
366999JiireNo

Please help to get the desired out-put.

Thanks in Advance.

1 Solution

Accepted Solutions
Highlighted

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;

View solution in original post

4 Replies
Highlighted

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;

View solution in original post

Highlighted
Creator III
Creator III

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);


Highlighted
Creator III
Creator III

Thanks Sunny It is working .

Highlighted
Creator III
Creator III

Thanks Thomas...This solution is also working.