topic Re: Need Help to merge tables in QlikView

Need Help to merge tables

Tue, 17 Jan 2017 15:45:25 GMT

Hello,

Thanks for your valuable input in advance....

I have a hard time in finding a resolution for the below issue..

I have 2 Tables with me..... One is Activity summary ( which has details of both open and closed activity) and the other one is open activity details....

Activity summary has multiple occurrence of same activity, for example (Sample)

Activity ID AverageResponsetime Totalturnaround

1 0 0

1 3 19

I have an SQL query to get the activity that has Totalturnaround,

SELECT [ACTIVITY_ID]

FROM (

SELECT *, CASE WHEN (max ([TURNAROUND_TIME]) OVER(PARTITION BY [ACTIVITY_ID]) = [TURNAROUND_TIME]) THEN 1 ELSE 0 END as MyRow

FROM [eGActiveDB].[dbo].[EGMLR_SMY_ACTIVITY] ) t

WHERE MyRow = 1 and (GMT_DATE > CONVERT(DATETIME, '2017-1-1 00:00:00', 102));

I have an other table that has open activity and I want to exclude open activity from the activity summary,

SELECT ACTIVITY_ID, DEPT_ID, DEPT_NAME, USER_ID, ENTRYPOINT_NAME, ENTRYPOINT_ADD, TURNAROUND_TIME, TURNAROUND_TIME_BH, RESPONSE_TIME_BH,

MET_SLA, IS_REPLIED, ACTIVITIES_TURNEDAROUND, ACTIVITY_SUB_TYPE_VALUE

FROM EGMLR_SMY_ACTIVITY

WHERE (NOT EXISTS

(SELECT ACTIVITY_ID

FROM EGPL_CASEMGMT_ACTIVITY_LT_9000

WHERE (EGMLR_SMY_ACTIVITY.ACTIVITY_ID = ACTIVITY_ID)));

Now I want to merge these two queries and load data into qlikview.....Load closed activities (that are not in open activity table and get unique value out the output)

Activity ID AverageResponsetime Totalturnaround

1 0 0 - activity OPen

1 3 19 - activity OPen

2 0 0 - activity Closed

2 3 22 - activity Closed

3 0 0 - activity Closed

3 3 33 - activity Closed

My end result has to be ,

Activity ID AverageResponsetime Totalturnaround

2 3 22 - activity Closed

3 3 33 - activity Closed

Please provide your expert view...

Re: Need Help to merge tables

rahulpawarb — Tue, 17 Jan 2017 17:59:29 GMT

Hello Saravanan,

Below is SQL Query level quick workaround:

1. Write a SQL Query to get all the activity records i.e. Open, Closed, with/without Turn-around etc.

2. Filter SQL Query written in step 1 by adding filter condition (In WHERE Clause) to get those records with Turn-around

3. Again filter SQL Query written in step 1 by adding filter condition - NOT EXISTS (In WHERE Clause) to exclude the Open activity records

Below is pseudo code:

SELECT a.*

FROM eGActiveDB.dbo.EGMLR_SMY_ACTIVITY a

WHERE 1 = 1

AND a.ACTIVITY_ID IN (

SELECT ACTIVITY_ID

FROM (

SELECT *,

CASE WHEN (max ([TURNAROUND_TIME]) OVER(PARTITION BY [ACTIVITY_ID]) = [TURNAROUND_TIME]) THEN 1 ELSE 0 END as MyRow

FROM eGActiveDB.dbo.EGMLR_SMY_ACTIVITY

) t

WHERE MyRow = 1

AND (GMT_DATE > CONVERT(DATETIME, '2017-1-1 00:00:00', 102))

)

AND NOT EXISTS (

SELECT 1

FROM eGActiveDB.dbo.EGMLR_SMY_ACTIVITY

WHERE NOT EXISTS (

SELECT ACTIVITY_ID

FROM EGPL_CASEMGMT_ACTIVITY_LT_9000

WHERE (eGActiveDB.dbo.EGMLR_SMY_ACTIVITY.ACTIVITY_ID = ACTIVITY_ID)

)

AND a.ACTIVITY_ID = eGActiveDB.dbo.EGMLR_SMY_ACTIVITY.ACTIVITY_ID

)

Hope this will solve the purpose.

Thank you!
Rahul

Re: Need Help to merge tables

Wed, 18 Jan 2017 11:50:14 GMT

Thank you Rahul. I took off NOT from your suggested code to get the output...

AND NOT EXISTS (
SELECT 1
FROM eGActiveDB.dbo.EGMLR_SMY_ACTIVITY
WHERE NOT EXISTS