Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 :
Hi Toufiq ,
Thanks for your reply ..
Do we have any other options to achieve it like in Front end can we do it .
Thanks
Hi Toufiq ,
When I applied this solution to my original table or data set then its not working ...
Thanks
attached the qlikview
can you share the error
When I am implement above logic I am getting 1 , 2 , 3 and - value in cc but that all values comes under - only
can you share your implementation
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;