topic Re: Set Analysis Expression for SQL query in QlikView

Set Analysis Expression for SQL query

Fri, 16 Aug 2013 07:32:09 GMT

Hi All....

Can anyone please provide me with the Set analysis expreesion in qlikview for the following sql code:

SELECT COUNT(DISTINCT EP.enc_proc_id) AS [Procs],

ECC.date_stamp_month,

ECC.date_stamp_year

FROM PATIENT_ENCOUNTER PE

INNER JOIN ENCOUNTER_PROCEDURE EP ON

PE.encounter_id = EP.encounter_id

INNER JOIN EOM_CLIENT_CYCLES ECC ON

EP.client_id = ECC.client_id AND

EP.enc_proc_date_of_entry BETWEEN ECC.open_date AND ECC.close_date

WHERE PE.client_id = 53 AND

PE.pat_enc_inv_flag IS NULL AND

EP.enc_proc_base_units IS NULL AND

EP.enc_proc_time_units IS NULL AND

EP.procedure_code_id NOT LIKE '%F' AND

ECC.date_stamp_year = 2013

GROUP BY ECC.date_stamp_month,

ECC.date_stamp_year

ORDER BY ECC.date_stamp_year, ECC.date_stamp_month

it is quite urgent and i am not getting as to how i should define the null condition in set analysis...

thanks..

Re: Set Analysis Expression for SQL query

Fri, 16 Aug 2013 07:37:50 GMT

nedd not worry about joining the tables...
it is just the where conditions that i am concerned about...

please help..

thanks

Re: Set Analysis Expression for SQL query

jagan — Fri, 16 Aug 2013 07:56:53 GMT

HI,

For EP.procedure_code_id get a field in select query with a flag like

Upper(Right(EP.procedure_code_id, 1)) as Flag and use the below expression

Sum({<PE.client_id = {53}, PE.pat_enc_inv_flag -={'*'}, EP.enc_proc_base_units -={*}, EP.enc_proc_time_units -={*},

ECC.date_stamp_year = {2013} , Flag={'F'}>} MeasureName)

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis Expression for SQL query

Fri, 16 Aug 2013 08:01:28 GMT

Jagan,

Just curious to know

PE.pat_enc_inv_flag -={'*'} Will this exclude all the values from pat_enc_inv_flag or only the NULL values..

Re: Set Analysis Expression for SQL query

Fri, 16 Aug 2013 08:29:35 GMT

for procedure_code_id i should get a field in select query with a flag ...

please explain...

i am using this expression :-

if(isnull(enc_proc_time_units) and isnull(enc_proc_base_units) and isnull(pat_enc_inv_flag),Count({$<procedure_code_id={'*'} - {'*f'}>}distinct(enc_proc_id)))

with the other filters of year and client getting selected from list boxes..

but it not giving the desired result..

i dnt understand where i am going wrong...

Re: Set Analysis Expression for SQL query

Fri, 16 Aug 2013 08:37:22 GMT

hi jagan... thanks for your rply...

but tthe expression you gave is showing some error...

i guess it is not taking -= correctly..

any other suggestions.. ??

Re: Set Analysis Expression for SQL query

jagan — Fri, 16 Aug 2013 08:37:47 GMT

Hi Jasper,

PE.pat_enc_inv_flag ={'*'} will give you all values without null, if you -= it will exclude all the values and gives you only the null values.

Regards,

Jagan.

Re: Set Analysis Expression for SQL query

jagan — Fri, 16 Aug 2013 08:39:26 GMT

Hi Rishabh,

Try this expression

Count({<PE.client_id = {53}, PE.pat_enc_inv_flag -={'*'}, EP.enc_proc_base_units -={*}, EP.enc_proc_time_units -={*}, ECC.date_stamp_year = {2013} , procedure_code_id={'*'} - {'*f'}>} distinct(enc_proc_id))

Hope it helps you.

Regards,

Jagan.

Re: Set Analysis Expression for SQL query

Fri, 16 Aug 2013 09:42:08 GMT

hey jagan...
thanks for your rply..

but as i said it is showing error...
and not giving the desired result...

it shows a red hazy line....

please help

Re: Set Analysis Expression for SQL query

jagan — Fri, 16 Aug 2013 09:49:44 GMT

Hi,

That red line is a bug in qlikview when you use -= it will show that, that is not a problem. If possible can you attach some sample file.

Regards,

Jagan.

Re: Set Analysis Expression for SQL query

jagan — Fri, 16 Aug 2013 09:49:50 GMT

Hi,

That red line is a bug in qlikview when you use -= it will show that, that is not a problem. If possible can you attach some sample file.

Regards,

Jagan.

Re: Set Analysis Expression for SQL query

Fri, 16 Aug 2013 10:00:24 GMT

I am attaching the image of the expression editor and the output that i am getting...
it is showing a syntax error...

sorry as i am working on server, i cant provide you the file for the same..

any help in this regard is appreciated...

thanks...

Re: Set Analysis Expression for SQL query

jagan — Wed, 06 Nov 2013 09:17:29 GMT

Hi,

Try giving * in single quotes, hope it works.

Regards,

Jagan.