Convert SQL query into Qlik Sense

[qlikwip2020]

Hi All /  @Gysbert_Wassenaar  , @swuehl  , @sunny_talwar , @Anil_Babu_Samineni 

I wanted to convert below SQL query into Qlik Sense app .

select

t1.name
,t2.postcode
,t1.polnumber as polnumber1
,t1.startdate as startdate1
,t1.package as package1
,t2.name as name2
,t2.polnumber as polnumber2
,t2.package at package2
,t2.startdate as startdate2

from
(
select
p.polnumber
,p.startdate
,p.package
,p.level
,c.name
,h.postcode
,h.housenum
,h.housename
,h.addre1
from
polQouet p
join
customer c
on p.polqouetid = c.polqouetid
join
home h
on p.polqouetid = h.polqouetid
where p.product= '1' ) as t1 ,

(
select
p.polnumber
,p.startdate
,p.package
,p.level
,c.name
,h.postcode
,h.housenum
,h.housename
,h.addre1
from
polQouet p
join
customer c
on p.polqouetid = c.polqouetid
join
home h
on p.polqouetid = h.polqouetid
where p.product= '1' ) as t2
where t1.name = t2.name and t1.postcode=t2.postcode and t1.polnumber != t2.polnumber
order by t1.polnumber

Please do the needful.

[qlikwip2020]

Can anyone help me on this???

[qlikwip2020]

Can anyone please help me on this . It will very appreciate.

[ArnadoSandoval]

Hi @qlikwip2020 

Is it possible to make a view out of this sql script at the database server? then you reference the view as any Database table!

Hope this helps,

[qlikwip2020]

Hi Arnado ,

No It is not possible to create view in database level. I need to implement it in Qlik Sense only.

Thanks.

[HirisH_V7]

Try out this:

///////SQL Query sTART////////
T1_Temp:
Load * ;
SQL SELECT 
p.polnumber
,p.startdate
,p.package
,p.level
,c.name
,h.postcode
,h.housenum
,h.housename
,h.addre1
from
polQouet p ;

Join

Load * where p.polqouetid = c.polqouetid; 
SQL  SELECT * From customer c ;

Join

Load * where p.polqouetid = h.polqouetid and  p.product= '1' ; 
SQL  SELECT * From home h;

NoConcatenate 

T2_Temp:
Load * ;
SQL SELECT 
p.polnumber
,p.startdate
,p.package
,p.level
,c.name
,h.postcode
,h.housenum
,h.housename
,h.addre1
from
polQouet p ;

Join

Load * where p.polqouetid = c.polqouetid; 
SQL  SELECT * From customer c ;

Join

Load * where p.polqouetid = h.polqouetid ; 
SQL  SELECT * From home h;
///////SQL Query end////////
Temp:
LOAD *,'T1'  as Flag Resident T1_Temp;
DROP Table T1_Temp;

LOAD *,'T2'  as Flag Resident T1_Temp;
DROP Table T2_Temp;

Final:
Load *
Resident Temp where 
t1.name = t2.name and t1.postcode=t2.postcode and t1.polnumber <> t2.polnumber  //you can change this accordingly as per data formed.

order by t1.polnumber;
DROP Table Temp;

[qlikwip2020]

Hi Hirish ,

Thanks for your reply ...

I have tried your query but I am getting exact output.

Basically what i want is , I want duplicate pol numbers based on same post code , address , house number.

Means Whoever have same post code, address but have two pol number we want that customers  with pol numbers 

Please find the attached sample file with expected output.

[HirisH_V7]

Check this:

Map:
Mapping
 Load Pick,Pol_Key where Pol_Key>=2;
LOAD *,
     Name&PostCode
     &[House Name]
     &[House Number]
     &[Flat No]
     &[Flat Name]&Adress as Pick,
      Autonumber(RowNumber,Name&PostCode
     &[House Name]
     &[House Number]
     &[Flat No]
     &[Flat Name]&Adress) As Pol_Key
     
FROM
[QS duplicate number.xlsx]
(ooxml, embedded labels, table is [SAmple Data]);

Rep_Table:
LOAD * where ApplyMap('Map',Pick)=2;
Load *,
     Name&PostCode
     &[House Name]
     &[House Number]
     &[Flat No]
     &[Flat Name]&Adress as Pick
FROM
[QS duplicate number.xlsx]
(ooxml, embedded labels, table is [SAmple Data]);​

[ArnadoSandoval]

@qlikwip2020 

You got this alternative as well; I added comments to each section of the script

NoConcatenate 
// We added a column on (Name + PostCode) as Rule_Group
Final:
LOAD
    RowNumber,
    Name,
    PostCode,
    "Pol Number",
    "House Name",
    "House Number",
    "Flat No",
    "Flat Name",
    Adress,
    Name & '|' & PostCode  As Rule_Group
FROM [lib://ConvertSQL/QS duplicate number.xlsx]
(ooxml, embedded labels, table is [SAmple Data]);

NoConcatenate
// Count Rule_Group and keep those records with a count >= 2
Tmp_Final:
Load Rule_Group,
     GroupTotal
Where GroupTotal >= 2
;
Load
     Rule_Group,
     Count(Rule_Group) As GroupTotal
Resident Final
Group By
     Rule_Group;

NoConcatenate
// Lets create New_Final by joining Final with Tmp_Final,
// which is done on the Rule_Group column
New_Final:
Load *
Resident Tmp_Final;
Inner Join Load * Resident Final;
// we drop work tables, and the Rule_Group column as it is not longer required.
Drop Table Tmp_Final;
Drop Table Final;
Drop Field Rule_Group;

 Hope this helps,

[HirisH_V7]

Check this using peek...

Temp:
LOAD *,
RecNo() as No,

     AutoNumberHash256(Name&PostCode
     &[House Name]
     &[House Number]
     &[Flat No]
     &[Flat Name]&Adress) as Pick
     
FROM
[QS duplicate number.xlsx]
(ooxml, embedded labels, table is [SAmple Data]);

Rep_Temp:
Load Pick,
If(Peek(Pick)=Pick,peek(Check)+1,1) as Check
Resident Temp order by Pick;
Rep_Map:
Mapping  
Load Pick,1 as Key  resident Rep_Temp where Check>=2;


Rep:
NoConcatenate
Load *
Resident Temp where ApplyMap('Rep_Map',Pick)=1;

DROP Table Temp,Rep_Temp;