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

[#1]

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

[jaimeaguilar]

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

[javier_florian]

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;