Expression syntax to exclude specific data set

[#1]

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)

[MayilVahanan]

Hi

     Try like this,

     =Subfield(FieldName,'.',1)

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

[#3]

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?

[MayilVahanan]

Hi

     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

     Or

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

    But not  sure..

[Alexander_Thor · Accepted solution]

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

[#6]

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?

[Alexander_Thor]

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 '%.%';