topic Re: Expression syntax to exclude specific data set in QlikView

Expression syntax to exclude specific data set

Tue, 14 Aug 2012 21:01:17 GMT

We have case numbers that I need to exclude from my trending.

Case numbers examples:

G2012-02534

G2012-03456

We have cases (addendums) where the case adds a .1 to the case number which need to be excluded from the query.

Case number examples to exclude from query

G2012-02534.1

G2012-03456.2

etc...

How I exclude '.#' cases in this statement:

count(distinct WP_CaseTable.Number)

Re: Expression syntax to exclude specific data set

MayilVahanan — Tue, 14 Aug 2012 21:20:28 GMT

Try like this,

=Subfield(FieldName,'.',1)

For example : =Subfield('G2012-02534.1','.',1) gives G2012-02534

Re: Expression syntax to exclude specific data set

Tue, 14 Aug 2012 21:30:27 GMT

Thanks for the quck reply.

If I am doing this correctly this is simply removing the .# from the case number. I want to exclude .# from the query so the cases do not show up.

So, if my expression is- count(distinct WP_CaseTable.Number)

How would I include your statement to count my distinct case numbers but exclude all '.#' cases from the count?

Re: Expression syntax to exclude specific data set

MayilVahanan — Tue, 14 Aug 2012 21:37:14 GMT

I can't get you.But something like this,

=Use in script

Load *, Subfield(FieldName,'.',1) as FieldName from table; And use it in expression

count(distinct WP_CaseTable.Number) - Count(distinct Subfield(WP_CaseTable.Number,'.',1))

But not sure..

Re: Expression syntax to exclude specific data set

Alexander_Thor — Tue, 14 Aug 2012 22:33:07 GMT

=Count( {$-1<Case={'*.*'}>} Case )

This will force an exlusion of any records in the field case containg a string value of ".".

Change case to your field and you should be good to go. However if you dont need those records in the data model I would recommend that you exlude them alltogether in the load script instead. Will make your expressions way easier to maintain

Attached is a sample.

Re: Expression syntax to exclude specific data set

Wed, 15 Aug 2012 13:51:17 GMT

Thanks Alexander! That worked.

However, I would like to build this into my script. I'm fairly new the QV- Could you explain how to incorporate this into the script?

Re: Expression syntax to exclude specific data set

Alexander_Thor — Wed, 15 Aug 2012 21:29:51 GMT

You should be able to add a where statement to your load statement. On a box that dont have QV installed so this is kinda psuedo code but should point you in the right direction.

Load *

from YourODBC

Where Case NOT LIKE '%.%';