Convert SQL query to qliksense script

[Syed17]

pts7.lease_id
,sum(PIs7.ACTUAL_AMOUNT) Total_Exported_Pay_Not_in_AP

FROM
pn_payment_items_all pis7,
pn_payment_terms_all pts7
where
pts7.lease_id=pss7.lease_id
and PTs7.PAYMENT_TERM_TYPE_CODE <>'TRNS'
and NVL(pis7.TRANSFERRED_TO_AP_FLAG,'N')='Y'
and
pis7.ap_invoice_num not in (select invs7.invoice_num from ap_invoices_all invs7 where INVs7.SOURCE = 'Oracle Property Manager')
group by
pts7.lease_id;     Can anyone help me to solve this query into qliksense script.

[rubenmarin]

Hi, you have the option to set this query directly in script, but if you want to use only one table each time it could be:

chkInvoiceNum:
LOAD invoice_num as chkInvoiceNum;
SELECT invs7.invoice_num 
FROM ap_invoices_all invs7 
WHERE INVs7.SOURCE = 'Oracle Property Manager';

chkLeaseId:
LOAD lease_id as chkLeaseId;
SELECT lease_id 
FROM pn_payment_terms_all pts7
WHERE PTs7.PAYMENT_TERM_TYPE_CODE <>'TRNS';

tmpItemsAll:
LOAD lease_id, ACTUAL_AMOUNT
Where Exists('chkLeaseId', lease_id)
  and not Exists('chkInvoiceNum',ap_invoice_num)
;
SELECT pis7.lease_id, pis7.ACTUAL_AMOUNT
FROM pn_payment_items_all pis7
WHERE NVL(pis7.TRANSFERRED_TO_AP_FLAG,'N')='Y';

FinalTable:
LOAD lease_id,
  sum(ACTUAL_AMOUNT) as Total_Exported_Pay_Not_in_AP
Resident tmpItemsAll
Group By lease_id;

DROP Tables chkInvoiceNum, chkLeaseId, tmpItemsAll;