topic Re: Very Urgent - Help Required on Set Analysis in QlikView

Very Urgent - Help Required on Set Analysis

Wed, 21 Aug 2013 11:37:41 GMT

Hi,

I want someone to please quickly write correct Expression for the below mentioned SQL query. Though it is simple but my expression is giving wrong results again and again. PFA is screenshot of Qlikview report. Kindly note Year and Customer are report selections here.

I am executing an SQL query at my database and I am getting expected values there.

Following is the query:

SELECT	COUNT(DISTINCT a.p_id) AS [Routine],
		b.month,
		b.year

FROM	Table1 a
		INNER JOIN Table2 b ON
		a.rountine_id = b.routine_id
		INNER JOIN Time c ON
		a.customer_id = c.customer_id AND
		a.date_of_routine BETWEEN c.open_date AND c.close_date

WHERE	b.customer_id = 5 AND
		a.flag IS NULL AND
		a.base IS NULL AND
		a.time_frame IS NULL AND
		a.routine_code_id NOT LIKE '%F' AND
		c.year = 2012

GROUP BY c.month,

c.year

ORDER BY c.year, c.month

Your quick response is required and shall be appreciated.

Thanks.

Busigence.

Re: Very Urgent - Help Required on Set Analysis

Wed, 21 Aug 2013 11:41:58 GMT

Im not sure i understand your demand.

Dont you have an datedimension?

Otherwise you could load this into the script:

Load*;

SELECT	COUNT(DISTINCT a.p_id) AS [Routine],
		b.month,
		b.year

FROM	Table1 a
		INNER JOIN Table2 b ON
		a.rountine_id = b.routine_id
		INNER JOIN Time c ON
		a.customer_id = c.customer_id AND
		a.date_of_routine BETWEEN c.open_date AND c.close_date

WHERE	b.customer_id = 5 AND
		a.flag IS NULL AND
		a.base IS NULL AND
		a.time_frame IS NULL AND
		a.routine_code_id NOT LIKE '%F' AND
		c.year = 2012

GROUP BY c.month,

c.year

ORDER BY c.year, c.month

Re: Very Urgent - Help Required on Set Analysis

alexandros17 — Wed, 21 Aug 2013 11:45:41 GMT

I think you need a graph with year and month as dimension and sum(Routine) as expression ...

Are you sure the query is correct?

Re: Very Urgent - Help Required on Set Analysis

orital81 — Wed, 21 Aug 2013 12:15:57 GMT

Hi Busigence

Since you have a "BETWEEN" function it you will need to use Interval Match function in your script.

This usualy make the querry more complicated, if you want a quick solution I would

take Thomas's suggestion and use the SQL as is:

connection string

Load *;

SQL Select ....

Ori