topic Re: Excluding data. in QlikView

Excluding data.

Thu, 17 Sep 2015 11:57:27 GMT

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?

Re: Excluding data.

Anonymous — Thu, 17 Sep 2015 12:00:48 GMT

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)

Re: Excluding data.

Thu, 17 Sep 2015 12:02:09 GMT

No i do want the yellow ones.

All i want to exclude is dark Blue.

Re: Excluding data.

jonathandienst — Thu, 17 Sep 2015 12:02:51 GMT

In script (QV):

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

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

Re: Excluding data.

jonathandienst — Thu, 17 Sep 2015 12:04:24 GMT

In set expression in the front end:

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

Re: Excluding data.

Anonymous — Thu, 17 Sep 2015 12:12:13 GMT

so skip first condition or use jonathans solution

jontydkpi‌

Re: Excluding data.

Thu, 17 Sep 2015 12:22:18 GMT

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.

Re: Excluding data.

tresB — Thu, 17 Sep 2015 12:28:38 GMT

Try like:

MACRecord:

LOAD

Kostenplaats,

Vestiging,

ObjectID,

ApprovalID

FROM

(qvd)

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

Re: Excluding data.

sasiparupudi1 — Thu, 17 Sep 2015 12:39:15 GMT

MACRecord:

LOAD

Kostenplaats,

Vestiging,

ObjectID,

ApprovalID

FROM

(qvd)

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

Re: Excluding data.

maxgro — Thu, 17 Sep 2015 12:44:22 GMT

add jontydkpi‌ suggestion after your from, example is

MACRecord:

LOAD

Kostenplaats,

Vestiging,

ObjectID,

ApprovalID

FROM

(qvd)

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

;