Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, How can I rewrite this query in Qliksense
Select distinct fl.license_no,
fad.state as state_code, fad.ref_id
from application_details fad
inner join license fl on fad.ref_id=fl.ref_id
inner join (
Select fl.license_no, max(fl.created_on)as max_date
from application_details fad inner join license fl on fad.ref_id=fl.ref_id
where to_char(fl.expiry_date,'yyyy-MM-dd')>='2022-08-31'
and fl.created_on <='2022-08-31'
group by fl.license_no) aa on fl.license_no=aa.license_no and fl.created_on=aa.max_date
where fad.status_id=5
and
(
select count(*) from process_log
where ref_id=fad.ref_id and processed_on=(
select max(processed_on)
from process_log where ref_id=fad.ref_id
and processed_on>aa.max_date and processed_on<='2022-08-31'
)
and status_id=9)=0
My recommendation would be to build an ETL were you first extract the clean tables tables and do the joins and condition in Qlik with Qvds and/or residents loads.
The qlik data manager wizard can help you a lot with that.
If you still want to just use the provided Sql you can try:
Table_name:
Load * //or list the fields names
;
SQL SELECT
<paste your query without your first select statement>
;
Best,