Set Analysis Expression for SQL query

[#1]

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..

[#2]

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

please help..

thanks

[jagan]

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.

[#4]

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..

[#5]

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...

[#6]

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.. ??

[jagan]

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.

[jagan]

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.

[#9]

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

[jagan]

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.