topic Duplicate policy number in QlikView

Duplicate policy number

qlikwip2020 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have one requirement that I need to find out duplicate policy number based on same Name and Post code.

i.e Whoever has two policy on one house (define based on same post code) we need to identify them.

Please find the attached excel for input and output

Re: Duplicate policy number

Taoufiq_Zarra — Fri, 29 May 2020 07:58:08 GMT

May be :

Data: LOAD rowno() as IDD,* INLINE [ Name, PostCode, Pol Number, House Name, House Number, Flat No, Flat Name, Adress John David, ABC001, PO123, L street, 12, A12, A, L street 12 A12 A John David, ABC001, MN569, L street, 12, A12, A, L street 12 A12 A Peter Parkar, PQN258, BC124, C phase, 14, C14, C, C phase 14 C14 C Peter Parkar, PQN258, LS198, C phase, 14, C14, C, C phase 14 C14 C Hegrid , HQ125, PO748, B Stree, 11, B11, B, B phase 11 C11 B Hegrid , BK579, PO547, M Street, 15, B15, B, B phase 11 C11 B Hegrid , HQ125, PO478, B Stree, 11, B11, B, B phase 11 C11 B Roan, M165, S15425, A steet, 13, B13, B, B phase 13 Roan, C548, B5245, S treet, 14, B14, B, B phase 13 Roan, F587, C1458, F Stree, 15, B15, B, B phase 13 Dreco, A8999, PO474747, C phase, 1, C1, C, C phase Dreco, A8999, PO889988, C phase, 1, C1, C, C phase Dreco, B254151, LO251478, S phase, 2, S1, S, S phase Dreco, B474747, MO66993, H phase, 2, S1, S, S phase Sedrik, C878787, LT00001, N phase, 18, S18 , S, D trek Sedrik, H4747, PT15456, I Pase, 54, I24, I, Igate Sedrik, C878787, LT00001, N phase, 18, S18 , S, D trek ]; output: noconcatenate load * where CC > 1; load count(Name&PostCode) as CC, Name&PostCode as ID resident Data group by Name,PostCode; left join load Name&PostCode as ID,* resident Data; drop table Data; drop fields ID,CC,IDD;

output :

Re: Duplicate policy number

qlikwip2020 — Fri, 29 May 2020 08:08:04 GMT

Hi Toufiq ,

Thanks for your reply ..

Do we have any other options to achieve it like in Front end can we do it .

Thanks

Re: Duplicate policy number

qlikwip2020 — Fri, 29 May 2020 09:08:22 GMT

Hi Toufiq ,

When I applied this solution to my original table or data set then its not working ...

Thanks

Re: Duplicate policy number

Taoufiq_Zarra — Fri, 29 May 2020 09:24:44 GMT

attached the qlikview

can you share the error

Re: Duplicate policy number

qlikwip2020 — Fri, 29 May 2020 10:00:47 GMT

When I am implement above logic I am getting 1 , 2 , 3 and - value in cc but that all values comes under - only

Re: Duplicate policy number

Taoufiq_Zarra — Fri, 29 May 2020 11:27:48 GMT

@qlikwip2020

can you share your implementation

Re: Duplicate policy number

Saravanan_Desingh — Sat, 06 Jun 2020 22:17:51 GMT

One solution is.

tab1: LOAD RowNo() As RowID, Name, PostCode, [Pol Number], [House Name], [House Number], [Flat No], [Flat Name], Adress FROM [C:\Users\sarav\Downloads\QS duplicate number.xlsx] (ooxml, embedded labels, table is [SAmple Data]); Left Join(tab1) LOAD PostCode, [House Name], [House Number], [Flat No], [Flat Name], Adress, Count([Pol Number]) As Cnt Resident tab1 Group By PostCode, [House Name], [House Number], [Flat No], [Flat Name], Adress ; tab2: NoConcatenate LOAD * Resident tab1 Where Cnt > 1 ; Drop Table tab1;

Re: Duplicate policy number

Saravanan_Desingh — Sat, 06 Jun 2020 22:18:43 GMT