topic Re: Can anybody help me with the Qlikview script for this sql query . in QlikView

Can anybody help me with the Qlikview script for this sql query .

Wed, 11 Dec 2013 16:18:23 GMT

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

Re: Can anybody help me with the Qlikview script for this sql query .

jaimeaguilar — Wed, 11 Dec 2013 16:26:41 GMT

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

WHEN ROI_NBR_SESSIONS_2013.ITEM_ID LIKE 'D%'
THEN LTRIM(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,0)

STORE QlikViewTable into Table.qvd(qvd);

regards

Re: Can anybody help me with the Qlikview script for this sql query .

javier_florian — Wed, 11 Dec 2013 16:44:12 GMT

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;