Excluding data.

[#1]

Hey all!

I am looking for a way to exclude certain data from my results.

On a daily business we receive information about our 'devices' each device has an unique ID.

However some devices have a Main and several Sub Units.

Then they have a -00 behind their ID for Main unit

Or -01/02/03/04 as a Sub unit as shown below:

Yellow is a normal Device, Dark blue is Main Unit, Ugly blue is Sub unit.

Now for my calculations i need to take all of them exept for the Main Units as they have no revenue and kill the average.

So my question to all of you is: how do i exclude those?

[Anonymous]

if you donot want the yellow onces (the devices) and not the main Units (dark blue) you may use

if (len(ApprovalID)>11) and right (ApprovalID,2) <> '00', ApprovalID)

the first condition skips the devices, while the second condition skips the main Units (ending with 00)

[#3]

No i do want the yellow ones.

All i want to exclude is dark Blue.

[jonathandienst]

In script (QV):

     Where Right(ApprovalID, 3) = '-00'

     or

     Where SubField(ApprovalID, '-', 3) = '00'

[jonathandienst]

In set expression in the  front end:

     {<ApprovalID -= {'*-00'}>}

[Anonymous]

so skip first condition or use jonathans solution

jontydkpi‌

[#7]

Im sorry but i really have no idea where to put that code in the script.

Say my script is:

MACRecord:

LOAD

     Kostenplaats,

     Vestiging,

     ObjectID,

     ApprovalID

FROM

(qvd);

Where the heck do i put that piece of code.

[tresesco · Accepted solution]

Try like:

MACRecord:

LOAD

     Kostenplaats,

     Vestiging,

     ObjectID,

     ApprovalID

FROM

(qvd)

where Not Wildmatch( ApprovalID, '*-00') ;

[sasiparupudi1]

MACRecord:

LOAD

     Kostenplaats,

     Vestiging,

     ObjectID,

     ApprovalID

FROM

(qvd)

where SubField(ApprovalID,'-',-1) <>'00'

[maxgro]

add jontydkpi‌  suggestion after your from, example is

MACRecord:

LOAD

     Kostenplaats,

     Vestiging,

     ObjectID,

     ApprovalID

FROM

(qvd)

Where Right(ApprovalID, 3) = '-00'

;