Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwip2020
Partner
Partner

Convert SQL query into Qlik Sense

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.

9 Replies
qlikwip2020
Partner
Partner
Author

Can anyone help me on this???

qlikwip2020
Partner
Partner
Author

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

ArnadoSandoval
Specialist II
Specialist II

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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
qlikwip2020
Partner
Partner
Author

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
Master
Master

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;

 

HirisH
“Aspire to Inspire before we Expire!”
qlikwip2020
Partner
Partner
Author

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
Master
Master

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]);​

 

 
 

 

HirisH
“Aspire to Inspire before we Expire!”
ArnadoSandoval
Specialist II
Specialist II

@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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
HirisH_V7
Master
Master

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;
HirisH
“Aspire to Inspire before we Expire!”