hey guys am new to Qlik am try to implement below SQL query into Qlik, tried writing the same query but it does not work. is there any to use the below query in Qlik
select RTRIM(jh.job_reference) || ';' ||
RTRIM(jh.bol_number) || ';' ||
RTRIM(je.container_number) || ';' ||
RTRIM(T1.disch_voyage_reference) || ';"' ||
RTRIM(replace(replace(vs.full_name, chr(13), ''), chr(34), '')) || '";' ||
RTRIM(nvl(jh.point_from,'\N')) || ';' ||
RTRIM(nvl(p1.full_name,'\N')) || ';' ||
RTRIM(jh.point_load) || ';' ||
RTRIM(nvl(jh.point_trans,'\N')) || ';' ||
RTRIM(jh.point_disch) || ';' ||
RTRIM(nvl(jh.point_to,'\N')) || ';' ||
RTRIM(nvl(p2.full_name,'\N')) || ';' ||
RTRIM(jh.movement_type) || ';' ||
RTRIM(jh.dest_movement_type) || ';' ||
RTRIM(jh.movement_terms) || ';' ||
RTRIM(jd.commodity_code) || ';"' ||
RTRIM(upper(REPLACE(REPLACE(replace(jd.description, chr(34), ''), chr(45), ''),chr(39), ''))) || '";' ||
RTRIM(CASE when SUBSTR(JH.POINT_LOAD,1,2) IN ('US','CA') and CASE WHEN SUBSTR(JH.POINT_LOAD,1,2) IN ('US','CA') THEN jh.alternate_point_export else jh.alternate_point_import end is null and SUBSTR(JH.MOVEMENT_TERMS,1,1)='D' THEN 'LD'
when SUBSTR(JH.POINT_LOAD,1,2) NOT IN ('US','CA') and CASE WHEN SUBSTR(JH.POINT_LOAD,1,2) IN ('US','CA') THEN jh.alternate_point_export else jh.alternate_point_import end is null and SUBSTR(JH.MOVEMENT_TERMS,2,1)='D' THEN 'LD'
WHEN SUBSTR(JH.POINT_LOAD,1,2) IN ('US','CA') then SUBSTR(JH.MOVEMENT_TERMS,1,1)
ELSE SUBSTR(JH.MOVEMENT_TERMS,2,1) END) || ';' ||
RTRIM(nvl(to_char(round(case when dims.UOM_WEIGHT = 'KGM' then dims.orig_weight*2.2 else dims.orig_weight end,2)),'\N')) || ';' ||
RTRIM(nvl(to_char(round((case when dims.UOM_WEIGHT = 'KGM' then dims.orig_weight*2.2 else dims.orig_weight end/2240),2)),'\N')) || ';' ||
RTRIM(jd.package_code) || ';' ||
RTRIM(nvl(to_char(JD.TEU_UTILISATION),'\N')) || ';' ||
RTRIM(jd.num_units) || ';' ||
RTRIM(JH.SHIPPING_TYPE) || ';' ||
RTRIM(JD.DTL_SEQUENCE) || ';' ||
RTRIM(dims.EQP_SEQUENCE) || ';' ||
RTRIM(to_char(new_time(sysdate,'GMT','EST'),'YYYY-MM-DD HH24:MM:SS'))
from
SCE.JOB_HEADERS JH
inner JOIN SCE.JOB_DETAILS JD ON JH.JOB_REFERENCE = JD.JOB_REFERENCE
inner JOIN SCE.JOB_EQUIPMENT JE ON JD.JOB_REFERENCE = JE.JOB_REFERENCE and JD.DTL_SEQUENCE = JE.DTL_SEQUENCE
inner join sce.job_dimensions dims on JE.JOB_REFERENCE = dims.job_reference and JE.DTL_SEQUENCE = dims.DTL_SEQUENCE and JE.EQP_SEQUENCE = dims.EQP_SEQUENCE
left outer join sce.ports p1 on jh.point_from = p1.point_code
left outer join sce.ports p2 on jh.point_to = p2.point_code
INNER JOIN (
SELECT
J.JOB_REFERENCE,
J.DISCH_VOYAGE_REFERENCE,
J.DELIV_DATE
FROM
JOURNEY J
INNER JOIN JOB_HEADERS JO ON JO.JOB_REFERENCE = J.JOB_REFERENCE AND JO.POINT_TRANS = J.POINT_TO
WHERE
J.JOURNEY_TYPE = 'T' AND
J.DISCH_VOYAGE_REFERENCE IS NOT NULL AND
J.DELIV_DATE >= (SYSDATE - 60) AND J.DELIV_DATE <= (SYSDATE + 1)) T1 ON T1.JOB_REFERENCE = JH.JOB_REFERENCE
INNER JOIN VOYAGES V ON V.VOYAGE_REFERENCE = T1.DISCH_VOYAGE_REFERENCE
INNER JOIN VESSELS VS ON VS.VESSEL = V.VESSEL
WHERE
(JH.POINT_LOAD LIKE 'US%' OR JH.POINT_LOAD LIKE 'CA%') OR
(JH.POINT_DISCH LIKE 'US%' OR JH.POINT_DISCH LIKE 'CA%');
