How to compare same record with a different ID in a Table

[sarsukanth]

Hi Everyone,

I have a difficult task at least for me in Qlik Sense Scripting.

Task is, I would like to get all the records in which the below field values are same but the IDs of those records are different (Example given below)

ArressLine1, ArressLine2, City, State, Country and Postal Code

So in the below example the result table should have only 4 records which IDs are 1,4,7 and 9

How can I get this result table through Qlik Sense Scripting. please help me.

Advance thank you for your support.

ID | Addr1 | Addr2 | City | State | Cntry | PIN
1 | ABC | DEF | GEH | IJK | LMN | 123456
2 | yw | ete | yer | ertu | ergfd | 423623
3 | fmdt | fuytu | iffd | fg | fgk | 663421
4 | ABC | DEF | GEH | IJK | LMN | 123456
5 | vm | jh | ut | tyur | ikd | 266234
6 | wqwe | twee | rerew | erq | qwr | 672315
7 | ABC | DEF | GEH | IJK | LMN | 123456
8 | asd | adfa | sdfsadf | asfas | dfas | 963242
9 | ABC | DEF | GEH | IJK | LMN | 123456

Thank you,

Raghu

[QFabian · Accepted solution]

Hi @sarsukanth , please try this

Data:
Load * INLINE [
ID,Addr1,Addr2,City,State,Cntry,PIN,
1,ABC,DEF,GEH,IJK,LMN,123456
2,yw,ete,yer,ertu,ergfd,423623
3,fmdt,fuytu,iffd,fg,fgk,663421
4,ABC,DEF,GEH,IJK,LMN,123456
5,vm,jh,ut,tyur,ikd,266234
6,wqwe,twee,rerew,erq,qwr,672315
7,ABC,DEF,GEH,IJK,LMN,123456
8,asd,adfa,sdfsadf,asfas,dfas,963242
9,ABC,DEF,GEH,IJK,LMN,123456
];

Load
if( rowno() = 1, ID, if( Addr1 = previous(Addr1) and Addr2 = previous(Addr2) and
City = previous(City) and State = previous(State) and
Cntry = previous(Cntry) and PIN = previous(PIN), ID)) as ID

Resident Data
ORder by
Addr1,Addr2,City,State,Cntry,PIN;

drop table Data;

[sarsukanth]

Hi @QFabian,

Thank you for your quick response, yes it is working for given dummy example.

Now I need to implement the same in the original.

Thank you,

Raghu

[sarsukanth]

Hi @QFabian  / All,
As I said I have tried to implement  the same in the original task, but no luck.
Originally I need to convert the below SQL Statement in to Qlik Sense Script then store the result table in a QVD file.
But in Qlik Sense Script I need to use TableQVD1.qvd in the place of Table1.
Please help me here and advance Thank you for your support.

SQL Statement :
Select DISTINCT
p1.party_id,
p1.addr_id,
p1.addr_line_1,
p1.addr_line_2,
p1.city,
p1.state,
p1.postal_cd_prim,
p1.country,
p1.valid_addr_ind,
p1.dmr_addr_status,
p1.status_cd
From
Table1 p1,
Table1 p2
Where p1.party_id = p2.party_id
And p1.addr_line_1 = p2.addr_line_1
And p1.addr_line_2 = p2.addr_line_2
And p1.city = p2.city
And p1.state = p2.state
And p1.postal_cd_prim = p2.postal_cd_prim
And p1.status_cd = 'A' and p2.status_cd = 'A'
And p1.addr_id != p2.addr_id

Thank you,
Raghu