Help Needed to Convert SQL query to Qliksense SaaS script .

PM_QlikDev — Tue, 27 Jan 2026 10:45:26 GMT

Hi All , i need your help on converting SQL query to its corresponding Qliksense script.

I tried making the associations between the tables being used here but the data is not matching. Also the similar tables are getting joined with different keys again after getting already joined earlier,so that's not understandable.I guess im missing out something ,if anybody could help me on this. Thanks in advance.

PFB the query:

select a14.INVO_DLOC_NO||To_Char(a14.INVO_NO) CustCol_355,

a14.INVO_STATUS INVO_STATUS,

a14.INVO_STATUS CustCol_46,

NVL((a12.IINV_INVO_DLOC_NO || a12.IINV_INVO_NO), 0) IINV_INVO_NO,

(a14.IINV_INVO_DLOC_NO || a14.IINV_INVO_NO) CustCol_383,

NVL(a15.INVO_STATUS, 0) INVO_STATUS0,

a15.INVO_STATUS INVO_STATUS1,

a18.CACC_NO CACC_NO,

a16.PROJ_NO PROJ_NO,

a18.CACC_BCON_EMPL_ID EMPL_ID,

(a129.EMPL_LAST_NAME || ', ' || a129.EMPL_FIRST_NAME) CustCol_53,

a122.DEPT_GLOB_CODE DEPT_GL_CODE,

a118.CLIE_NAME CLIE_NAME,

a118.CLIE_NO CLIE_NO,

a112.CLIE_NAME CLIE_NAME0,

a112.CLIE_NO CLIE_NO0,

a111.CACC_NO CACC_NO0,

a13.PROJ_NO PROJ_NO0,

a111.CACC_BCON_EMPL_ID EMPL_ID0,

(a128.EMPL_LAST_NAME || ', ' || a128.EMPL_FIRST_NAME) CustCol_95,

a116.DEPT_GLOB_CODE DEPT_GLOB_CODE,

a120.COMP_NO "_EMP_COMP_ID",

a120.COMP_NAME COMP_NAME,

a120.COMP_CTRY_CODE EMP_CTRY_CODE,

a127.CTRY_NAME CustCol_76,

a119.OFFI_NO OFFI_NO,

a119.OFFI_NAME CustCol_5,

a122.DEPT_NAME CustCol_86,

a122.DEPT_NO DEPT_NO,

a114.COMP_NO COMP_NO,

a114.COMP_NAME COMP_NAME0,

a115.CTRY_CODE CTRY_NAME,

a115.CTRY_NAME CTRY_NAME0,

a113.OFFI_NO OFFI_NO0,

a113.OFFI_NAME OFFI_NAME,

a116.DEPT_NO DEPT_NO0,

a116.DEPT_NAME DEPT_NAME,

a18.CACC_AMET_CODE CACC_AMET_CODE,

a121.REFC_DESCRIPTION REFC_DESCRIPTION,

a14.INVO_REMARK IINV_REMARK,

a14.INVO_REVM_PERIOD INVO_REVM_PERIOD,

trim(to_char(to_date(a124.REVM_PERIOD,'yyyymm'),'Month'))||' '||substr(a124.REVM_PERIOD,0,4) CustCol_159,

a17.CCON_EMPL_ID EMPL_ID1,

(a125.EMPL_LAST_NAME || ', ' || a125.EMPL_FIRST_NAME) CustCol_454,

a110.CCON_EMPL_ID EMPL_ID2,

(a126.EMPL_LAST_NAME || ', ' || a126.EMPL_FIRST_NAME) CustCol_4540,

a13.PROJ_CACC_NO CACC_NO1,

a13.PROJ_CACC_CLIE_ID CACC_CLIE_ID,

a16.PROJ_CACC_CLIE_ID CACC_CLIE_ID0,

a16.PROJ_CACC_NO CACC_NO2,

a18.CACC_NAME CACC_NAME,

a13.PROJ_CACC_NO CACC_NO3,

a13.PROJ_CACC_CLIE_ID CACC_CLIE_ID1,

a111.CACC_NAME CACC_NAME0,

a18.CACC_NAME ENG_NAME,

a18.CACC_CLIE_ID CACC_CLIE_ID2,

a18.CACC_NO CACC_NO4,

sum(Nvl(a11.IPRO_FEE,0) + Nvl(a11.IPRO_EXPENSE,0) + NVL(a11.IPRO_VOLUME_EXTRAS,0)) Sending Currency,

sum(Nvl(a11.IPRO_BILL_FEE,0) + Nvl(a11.IPRO_BILL_EXPENSE,0) + NVL(a11.IPRO_BILL_VOLUME_EXTRAS,0)) WJXBFS2

from CA_INVOICE_PROJECT a11

left outer join CA_INTERNAL_INVOICES a12

on (a11.IPRO_INVO_ID = a12.IINV_INVO_ID)

left outer join CA_PROJECTS a13

on (a12.IINV_CACC_NO_TO = a13.PROJ_CACC_NO and

a12.IINV_CLIE_ID_TO = a13.PROJ_CACC_CLIE_ID and

a12.IINV_PROJ_NO_TO = a13.PROJ_NO)

left outer join (SELECT

-- Internal Invoice

a11.INVO_ID,a11.INVO_DLOC_NO, a11.INVO_NO,

a11.INVO_STATUS, a11.INVO_REVM_PERIOD, a11.INVO_TYPE, a11.INVO_SENT_DATE, a11.INVO_DATE_CR,

a11.INVO_GL_SENT, a11.INVO_GL_DATE, a11.INVO_REMARK,

-- External Invoice

a12.IINV_INVO_DLOC_NO, a12.IINV_INVO_NO,

a12.IINV_INVO_STATUS , a12.IINV_TRANSFER_FLAG, a12.IINV_CACC_NO_TO,

a12.IINV_CLIE_ID_TO, a12.IINV_PROJ_NO_TO, a12.TOTAL, a12.IINV_REMARK

FROM CA_INVOICES a11

LEFT JOIN (

SELECT DISTINCT IINV_INVO_ID, IINV_INVO_DLOC_NO,IINV_INVO_STATUS,IINV_INVO_NO,

IINV_TRANSFER_FLAG, SUM(DISTINCT IINV_AMOUNT) Total,

MAX(IINV_REMARK) IINV_REMARK, IINV_CACC_NO_TO, IINV_CLIE_ID_TO, IINV_PROJ_NO_TO

FROM CA_INTERNAL_INVOICES

GROUP BY IINV_INVO_ID, IINV_INVO_DLOC_NO,IINV_INVO_STATUS,IINV_INVO_NO,

IINV_TRANSFER_FLAG,IINV_CACC_NO_TO, IINV_CLIE_ID_TO, IINV_PROJ_NO_TO

) a12

ON case when a11.invo_status = 'REVR' then a11.invo_applied_id else a11.INVO_ID end = a12.IINV_INVO_ID) a14

on (a11.IPRO_INVO_ID = a14.INVO_ID and

a12.IINV_CACC_NO_TO = a14.IINV_CACC_NO_TO and

a12.IINV_CLIE_ID_TO = a14.IINV_CLIE_ID_TO and

a12.IINV_PROJ_NO_TO = a14.IINV_PROJ_NO_TO)

left outer join IFCAS.CA_INVOICES a15

on (a14.IINV_INVO_DLOC_NO = a15.INVO_DLOC_NO and

a14.IINV_INVO_NO = a15.INVO_NO)

left outer join CA_PROJECTS a16

on (a11.IPRO_CACC_CLIE_ID = a16.PROJ_CACC_CLIE_ID and

a11.IPRO_CACC_NO = a16.PROJ_CACC_NO and

a11.IPRO_PROJ_NO = a16.PROJ_NO)

left outer join CA_CO_CONSULTANTS a17

on (a16.PROJ_CACC_CLIE_ID = a17.CCON_CLIE_ID and

a16.PROJ_CACC_NO = a17.CCON_CACC_NO)

left outer join IFCAS.CA_CLIENT_ACCOUNT_HISTORY a18

on (a16.PROJ_CACC_CLIE_ID = a18.CACC_CLIE_ID and

a16.PROJ_CACC_NO = a18.CACC_NO)

left outer join CA_PROJECTS a19

on (a12.IINV_CACC_NO_TO = a19.PROJ_CACC_NO and

a12.IINV_CLIE_ID_TO = a19.PROJ_CACC_CLIE_ID and

a12.IINV_PROJ_NO_TO = a19.PROJ_NO and

a13.PROJ_OWN_DEPT_ID = a19.PROJ_OWN_DEPT_ID)

left outer join CA_CO_CONSULTANTS a110

on (a16.PROJ_CACC_CLIE_ID = a110.CCON_CLIE_ID and

a17.CCON_EMPL_ID = a110.CCON_EMPL_ID and

a19.PROJ_CACC_CLIE_ID = a110.CCON_CLIE_ID and

a19.PROJ_CACC_NO = a110.CCON_CACC_NO)

left outer join IFCAS.CA_CLIENT_ACCOUNT_HISTORY a111

on (a13.PROJ_CACC_CLIE_ID = a111.CACC_CLIE_ID and

a13.PROJ_CACC_NO = a111.CACC_NO)

left outer join CA_CLIENTS a112

on (a12.IINV_CLIE_ID_TO = a112.CLIE_ID)

left outer join CA_OFFICES a113

on (a111.CACC_OFFI_ID = a113.OFFI_ID)

left outer join CA_COMPANIES a114

on (a113.OFFI_COMP_ID = a114.COMP_ID)

left outer join CA_COUNTRIES a115

on (a114.COMP_CTRY_CODE = a115.CTRY_CODE)

left outer join CA_DEPARTMENTS a116

on (a111.CACC_DEPT_ID = a116.DEPT_ID)

left outer join CA_REFERENCE_CODES a117

on (a116.DEPT_GLOB_CODE = a117.REFC_CODE)

left outer join CA_CLIENTS a118

on (a16.PROJ_CACC_CLIE_ID = a118.CLIE_ID)

left outer join CA_OFFICES a119

on (a18.CACC_OWN_OFFI_ID = a119.OFFI_ID)

left outer join CA_COMPANIES a120

on (a119.OFFI_COMP_ID = a120.COMP_ID)

left outer join (SELECT DISTINCT B.REFC_DESCRIPTION, B.REFC_CODE_TYPE, B.REFC_CODE

FROM CA_REFERENCE_CODES B

WHERE B.REFC_CODE_TYPE = 'AMET') a121

on (a18.CACC_AMET_CODE = a121.REFC_CODE)

left outer join CA_DEPARTMENTS a122

on (a18.CACC_OWN_DEPT_ID = a122.DEPT_ID)

left outer join CA_REFERENCE_CODES a123

on (a117.REFC_CODE_TYPE = a123.REFC_CODE_TYPE and

a122.DEPT_GLOB_CODE = a123.REFC_CODE)

left outer join (select distinct revm_period,max(REVM_TIME_CUT_OFF_DATE) as REVM_TIME_CUT_OFF_DATE

from CA_REVENUE_MONTHS_ALL

where

(revm_time_cut_off_date between to_date('01/12/2006','dd/mm/yyyy') and last_day(sysdate))

group by revm_period

) a124

on (a14.INVO_REVM_PERIOD = a124.REVM_PERIOD)

left outer join IFCAS.CA_EMPLOYEES a125

on (a17.CCON_EMPL_ID = a125.EMPL_ID)

left outer join IFCAS.CA_EMPLOYEES a126

on (a110.CCON_EMPL_ID = a126.EMPL_ID)

left outer join CA_COUNTRIES a127

on (a120.COMP_CTRY_CODE = a127.CTRY_CODE)

left outer join IFCAS.CA_EMPLOYEES a128

on (a111.CACC_BCON_EMPL_ID = a128.EMPL_ID)

left outer join IFCAS.CA_EMPLOYEES a129

on (a18.CACC_BCON_EMPL_ID = a129.EMPL_ID)

where (a14.INVO_REVM_PERIOD in (202404)

and a14.INVO_TYPE in ('N')

and a120.COMP_NO in (N'AE01')

and a114.COMP_NO in ('AR01')

and a123.REFC_CODE in ('2300')

and a117.REFC_CODE in ('2400'))

group by a14.INVO_DLOC_NO||To_Char(a14.INVO_NO),

a14.INVO_STATUS,

NVL((a12.IINV_INVO_DLOC_NO || a12.IINV_INVO_NO), 0),

(a14.IINV_INVO_DLOC_NO || a14.IINV_INVO_NO),

NVL(a15.INVO_STATUS, 0),

a15.INVO_STATUS,

a18.CACC_NO,

a16.PROJ_NO,

a18.CACC_BCON_EMPL_ID,

(a129.EMPL_LAST_NAME || ', ' || a129.EMPL_FIRST_NAME),

a122.DEPT_GLOB_CODE,

a118.CLIE_NAME,

a118.CLIE_NO,

a112.CLIE_NAME,

a112.CLIE_NO,

a111.CACC_NO,

a13.PROJ_NO,

a111.CACC_BCON_EMPL_ID,

(a128.EMPL_LAST_NAME || ', ' || a128.EMPL_FIRST_NAME),

a116.DEPT_GLOB_CODE,

a120.COMP_NO,

a120.COMP_NAME,

a120.COMP_CTRY_CODE,

a127.CTRY_NAME,

a119.OFFI_NO,

a119.OFFI_NAME,

a122.DEPT_NAME,

a122.DEPT_NO,

a114.COMP_NO,

a114.COMP_NAME,

a115.CTRY_CODE,

a115.CTRY_NAME,

a113.OFFI_NO,

a113.OFFI_NAME,

a116.DEPT_NO,

a116.DEPT_NAME,

a18.CACC_AMET_CODE,

a121.REFC_DESCRIPTION,

a14.INVO_REMARK,

a14.INVO_REVM_PERIOD,

trim(to_char(to_date(a124.REVM_PERIOD,'yyyymm'),'Month'))||' '||substr(a124.REVM_PERIOD,0,4),

a17.CCON_EMPL_ID,

(a125.EMPL_LAST_NAME || ', ' || a125.EMPL_FIRST_NAME),

a110.CCON_EMPL_ID,

(a126.EMPL_LAST_NAME || ', ' || a126.EMPL_FIRST_NAME),

a13.PROJ_CACC_NO,

a13.PROJ_CACC_CLIE_ID,

a16.PROJ_CACC_CLIE_ID,

a16.PROJ_CACC_NO,

a18.CACC_NAME,

a13.PROJ_CACC_NO,

a13.PROJ_CACC_CLIE_ID,

a111.CACC_NAME,

a18.CACC_NAME,

a18.CACC_CLIE_ID,

a18.CACC_NO;

Output:

Sending Currency for INVO_REVM_PERIOD = 202404 should be 206221289

topic Help Needed to Convert SQL query to Qliksense SaaS script . in Move to Qlik Cloud Analytics

Help Needed to Convert SQL query to Qliksense SaaS script .