Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
SELECT
DISTINCT ROI_NBR_SESSIONS_2013.AS_OF_DATE,
ROI_NBR_SESSIONS_2013.RUN_ON_DATE,
ROI_NBR_SESSIONS_2013.ITEM_ID,
ROI_PRODUCT_HIER_2013.ITEM0100_ITEM_DESC,
ROI_NBR_SESSIONS_2013.ACCOUNT_NO,
CASE WHEN ROI_NBR_SESSIONS_2013.ITEM_ID LIKE '%CB%'
THEN LTRIM(SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,3,8),0)||'-' ||LTRIM(SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,11),0)
WHEN ROI_NBR_SESSIONS_2013.ITEM_ID LIKE 'D%'
THEN LTRIM(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,0)
WHEN ROI_NBR_SESSIONS_2013.ITEM_ID IN ('L_R6R7','L_R7','LR_R6R7')
THEN SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,3,9)
WHEN ROI_NBR_SESSIONS_2013.ITEM_ID = 'LI_NRR6R7' AND (ROI_NBR_SESSIONS_2013.ACCOUNT_NO = '1528-MU000140' OR ROI_NBR_SESSIONS_2013.ACCOUNT_NO LIKE '205-%')
THEN SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,-8)
ELSE ROI_NBR_SESSIONS_2013.ACCOUNT_NO END AS InSite_Acct,
ROI_NBR_SESSIONS_2013.ROLE_ID,
ROI_NBR_SESSIONS_2013.EMP_ID,
ROI_NBR_SESSIONS_2013.CUSTOMER_NAME,
ROI_NBR_SESSIONS_2013.REVENUE
FROM CMIC.ROI_NBR_SESSIONS_2013 ROI_NBR_SESSIONS_2013
INNER JOIN
CMIC.ROI_PRODUCT_HIER_2013 ROI_PRODUCT_HIER_2013
ON (ROI_NBR_SESSIONS_2013.ITEM_ID = ROI_PRODUCT_HIER_2013.ITEM0100_ITEM_ID)
WHERE (ROI_NBR_SESSIONS_2013.ROLE_ID IN 'CLADVISOR', 'CLOSER', 'TIP_PWM', 'IIS_TIP'))
AND (ROI_NBR_SESSIONS_2013.AS_OF_DATE = TO_DATE ('2013-10-31 00:00:00', 'yyyy/mm/dd hh24:mi:ss'))
ORDER BY ROI_NBR_SESSIONS_2013.ITEM_ID
Hi,
You can execute that script directly in QlikView, store it in a QVD file and then do any transformation you need:
QlikViewTable:
LOAD * ;
SQL
SELECT
DISTINCT ROI_NBR_SESSIONS_2013.AS_OF_DATE,
ROI_NBR_SESSIONS_2013.RUN_ON_DATE,
ROI_NBR_SESSIONS_2013.ITEM_ID,
ROI_PRODUCT_HIER_2013.ITEM0100_ITEM_DESC,
ROI_NBR_SESSIONS_2013.ACCOUNT_NO,
CASE WHEN ROI_NBR_SESSIONS_2013.ITEM_ID LIKE '%CB%'
THEN LTRIM(SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,3,8),0)||'-' ||LTRIM(SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,11),0)
WHEN ROI_NBR_SESSIONS_2013.ITEM_ID LIKE 'D%'
THEN LTRIM(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,0)
WHEN ROI_NBR_SESSIONS_2013.ITEM_ID IN ('L_R6R7','L_R7','LR_R6R7')
THEN SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,3,9)
WHEN ROI_NBR_SESSIONS_2013.ITEM_ID = 'LI_NRR6R7' AND (ROI_NBR_SESSIONS_2013.ACCOUNT_NO = '1528-MU000140' OR ROI_NBR_SESSIONS_2013.ACCOUNT_NO LIKE '205-%')
THEN SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,-8)
ELSE ROI_NBR_SESSIONS_2013.ACCOUNT_NO END AS InSite_Acct,
ROI_NBR_SESSIONS_2013.ROLE_ID,
ROI_NBR_SESSIONS_2013.EMP_ID,
ROI_NBR_SESSIONS_2013.CUSTOMER_NAME,
ROI_NBR_SESSIONS_2013.REVENUE
FROM CMIC.ROI_NBR_SESSIONS_2013 ROI_NBR_SESSIONS_2013
INNER JOIN
CMIC.ROI_PRODUCT_HIER_2013 ROI_PRODUCT_HIER_2013
ON (ROI_NBR_SESSIONS_2013.ITEM_ID = ROI_PRODUCT_HIER_2013.ITEM0100_ITEM_ID)
WHERE (ROI_NBR_SESSIONS_2013.ROLE_ID IN 'CLADVISOR', 'CLOSER', 'TIP_PWM', 'IIS_TIP'))
AND (ROI_NBR_SESSIONS_2013.AS_OF_DATE = TO_DATE ('2013-10-31 00:00:00', 'yyyy/mm/dd hh24:mi:ss'))
ORDER BY ROI_NBR_SESSIONS_2013.ITEM_ID;
STORE QlikViewTable into Table.qvd(qvd);
regards
Hi aptha,
You can use that script (another one) in QV, just use the follow structure:
ODBC/OLDB Connect ....
Internal_QlikView_name_table:
SQL Select ...
From...
Where ...
Order by...
;
Disconnect;