Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can anyone help me on this???
Can anyone please help me on this . It will very appreciate.
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,
Hi Arnado ,
No It is not possible to create view in database level. I need to implement it in Qlik Sense only.
Thanks.
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;
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.
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]);
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,
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;