Qlikview to SQL conversion

Question

Can anyone help to convert the below qlikview code to snowflake SQL?

I just started with qlikview to snowflake migration and have very minimal knowledge on qlikview so need help on how applymap works here:

Left Join(EXCEL_REQ_PO_MSTR)
LOAD PO_STATUS_KEY,IF(POD_SCHED='1',ApplyMap('EXCEL_PO_STATUS_SCHEDULED',PO_STATUS_KEY,null()),
ApplyMap('EXCEL_PO_STATUS_UNSCHEDULED',POD_NBR,null()))
AS PO_STAT_DESC
RESIDENT EXCEL_REQ_PO_MSTR;

EXCEL_PO_STATUS_SCHEDULED:
MAPPING
LOAD PO_STATUS_KEY,PO_STAT;
SQL SELECT DISTINCT CONCAT_WS('|',POD_NBR,POD_LINE,POD_PART,POD_LOC,POD_PO_SITE,POD_DUE_DATE) as PO_STATUS_KEY,
/*SELECT DISTINCT CONCAT_WS('|',POD_NBR,POD_LINE,POD_PART,POD_LOC,POD_SITE,POD_DUE_DATE) as PO_STATUS_KEY,*/
CASE
WHEN  "POD_END_EFF##1" IS NULL
THEN 'open'
ELSE 'closed'
END AS PO_STAT
FROM  PROD_RAW.EXCEL_QADDB.VW_POD_DET
WHERE  LENGTH(TRIM(POD_NBR))>0 /*AND
 SUBSTR(TRIM(POD_NBR),1,1) IN ('P')*/ AND SUBSTR(TRIM(POD_NBR),2,1) IN ('0','1','2','3','4','5','6','7','8','9') 
 AND  LENGTH(TRIM(POD_PART))>0;

EXCEL_PO_STATUS_UNSCHEDULED:
MAPPING
LOAD PO_STATUS_KEY,PO_STAT;
SQL SELECT DISTINCT PO_NBR AS PO_STATUS_KEY,CASE
WHEN  PO_STAT IS NULL
THEN 'open'
ELSE 'closed'
END AS PO_STAT
FROM PROD_RAW.EXCEL_QADDB.VW_PO_MSTR;

I tried below conversion:

SELECT
    CASE
        WHEN POD_SCHED = '1' THEN
            (SELECT PO_STATUS_KEY FROM dev_marts.purchasing.EXCEL_PO_STATUS_SCHEDULED)
        ELSE
            (SELECT pod_nbr FROM dev_marts.purchasing.EXCEL_PO_STATUS_UNSCHEDULED)
    END AS PO_STAT_DESC 
from dev_marts.purchasing.EXCEL_REQ_PO_MSTR;
 

Answer 1

Here is an example using some made up fields that are much simpler than the example you gave but you will be able to apply the concept.

Qlik Script:

DESC_MAP:
MAPPING
LOAD
ItemNum as ItemCode,
ItemName as Desc
FROM [lib://QVData/UserDescriptions.qvd]
(qvd);

ITEMS:
LOAD
ItemCode,
applymap('DESC_MAP', ItemCode, null()) as UserDesc
FROM [lib://QVData/Items.qvd]
(qvd);   

is equivalent to

select  
ItemCode,
(Select UserDescriptions.ItemName  FROM UserDescriptions where Items.ItemCode = UserDescriptions.ItemNum) as UserDesc
from Items

You should be able to just paste your complicated map creation SQL queries into your CASE query logic, then you still need to add the POSTATUS_KEY to the where clause to make it return a unique line each time