Duplicate policy number

[qlikwip2020]

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 

[Taoufiq_Zarra]

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 :

[qlikwip2020]

Hi Toufiq ,

Thanks for your reply ..

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

Thanks

[qlikwip2020]

Hi Toufiq ,

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

Thanks

[Taoufiq_Zarra]

attached the qlikview

can you share the error

[qlikwip2020]

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

[Taoufiq_Zarra]

@qlikwip2020 

can you share your implementation

[Saravanan_Desingh]

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;
     

[Saravanan_Desingh]