Help Needed to Convert SQL query to Qliksense SaaS script .

[PM_QlikDev]

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,

                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