topic Re: Filtering criteria in a table box in QlikView

Filtering criteria in a table box

Mon, 07 Apr 2014 03:54:09 GMT

I would like to filter what kind of results are shown in a table box, without altering my SQL script. Is this posible? So for instance, I want 1 table box to show only results of jobs which did not complete normally (ie: and completion_text not like '%Normal%'). In another table box I want to show jobs which are pending as opposed to Complete (ie: and status_code <> 'C'). Lastly, I'd like a table box which has the jobs which took over 5 mins to complete. All of these details are within a SQL query that I do not want to limit the results. Can I do these rules within a table box using the Calculation Condition on the Properties General Tab?

Re: Filtering criteria in a table box

Anonymous — Mon, 07 Apr 2014 03:58:55 GMT

Why not you use Straight table instead of Table box.

It can be easily used as Table box, where you can hide the columns which are calculated and your dont wanna show them in the table.

If you can you can provide me sample data, I'll show you.

Re: Filtering criteria in a table box

Mon, 07 Apr 2014 17:44:52 GMT

Hi Nitin - I am not sure how much data to give you, but here is my query. I want to add logic into my tables, to limit the table contents to only completed jobs, jobs that did not complete normally, and jobs that took over 5 mins. So, the data will be there, just want to surpress results when they are 'Normal Completion' or status_code <> 'C' etc.

select
cr.request_id,

fu.user_name,

fr.responsibility_name,

cp.user_concurrent_program_name,

cr.phase_code,

cr.status_code,

cr.argument_text,

cr.completion_text,

to_char(cr.requested_start_date, 'DD-Mon-YYYY HH24:MI:SS') request_start_date,

to_char(cr.actual_start_date, 'DD-Mon-YYYY HH24:MI:SS') actual_start_date,

to_char(cr.actual_completion_date, 'DD-Mon-YYYY HH24:MI:SS') actual_completion_date,

(CASE

WHEN cp.user_concurrent_program_name = 'Material Cost Collection Worker' THEN

(select organization_code from org_organization_definitions where organization_id = fcr.argument2)

WHEN cp.user_concurrent_program_name IN ('Cost Collection Manager','Discrete Job Value Report - Standard Costing','Close Discrete Jobs') THEN

(select organization_code from org_organization_definitions where organization_id = fcr.argument1)

ELSE

'N/A'

END) inv_org

from

FND_CONC_REQ_SUMMARY_V cr,

FND_USER fu,

FND_RESPONSIBILITY_VL FR,

FND_CONCURRENT_PROGRAMS_VL CP,

FND_CONCURRENT_REQUESTS fcr

where fcr.request_id = cr.request_id

and cr.requested_by = fu.user_id

AND fr.responsibility_id = cr.responsibility_id

AND cr.concurrent_program_id = cp.concurrent_program_id

and cr.actual_start_date >= sysdate - 30

and fu.user_name in ('INV_CLOSE','HANNAHP', 'LSTOTTS','HSIMON')

order by cr.requested_start_date desc, fr.responsibility_name

Re: Filtering criteria in a table box

alexpanjhc — Mon, 07 Apr 2014 18:32:27 GMT

in table box, you can set a drop down for each field. you can set them in presentation tab and check the dropdown box

you can then only selecting the creteria you need.

Re: Filtering criteria in a table box

maxgro — Mon, 07 Apr 2014 18:39:25 GMT

I think you can add some flag to your script (not the sql side, the qlikview, bold)

and then create a tablebox and some listbox to select your conditions

load

if(not wildmatch(completion_text, '*Normal*'),'Not normally','Normally') as FlagNotNormally,

if(status_code<>'C', 'Pending', 'Not pendiong') as FlagPending //,

//if(actual_completion_date - ................................) // to be completed

;

sql

select

cr.request_id,

fu.user_name,

fr.responsibility_name,

cp.user_concurrent_program_name,

cr.phase_code,

cr.status_code,

cr.argument_text,

cr.completion_text,

to_char(cr.requested_start_date, 'DD-Mon-YYYY HH24:MI:SS') request_start_date,

to_char(cr.actual_start_date, 'DD-Mon-YYYY HH24:MI:SS') actual_start_date,

to_char(cr.actual_completion_date, 'DD-Mon-YYYY HH24:MI:SS') actual_completion_date,

(CASE

WHEN cp.user_concurrent_program_name = 'Material Cost Collection Worker' THEN

(select organization_code from org_organization_definitions where organization_id = fcr.argument2)

WHEN cp.user_concurrent_program_name IN ('Cost Collection Manager','Discrete Job Value Report - Standard Costing','Close Discrete Jobs') THEN

(select organization_code from org_organization_definitions where organization_id = fcr.argument1)

ELSE

'N/A'

END) inv_org

from

FND_CONC_REQ_SUMMARY_V cr,

FND_USER fu,

FND_RESPONSIBILITY_VL FR,

FND_CONCURRENT_PROGRAMS_VL CP,

FND_CONCURRENT_REQUESTS fcr

where fcr.request_id = cr.request_id

and cr.requested_by = fu.user_id

AND fr.responsibility_id = cr.responsibility_id

AND cr.concurrent_program_id = cp.concurrent_program_id

and cr.actual_start_date >= sysdate - 30

and fu.user_name in ('INV_CLOSE','HANNAHP', 'LSTOTTS','HSIMON')

order by cr.requested_start_date desc, fr.responsibility_name