Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Exclude a set of data if a specific record exists within the data


I have the following set of data where an object number will have many different staus

objectnoFormattedNumber1projectstatus
PR0000658802.000409.01.01E0001
PR0000658802.000409.01.01I0001
PR0000658802.000409.01.01I0002
PR0000658802.000409.01.01I0045
PR0000658802.000409.01.01I0046
PR0000658802.000409.01.01I0118

Where the project status = I0046 I want to exclude all of the records applicable to the object number,if I0046 doesn't exist then I would want to display the records

Can anyone help

15 Replies
anbu1984
Master III
Master III

Do you have spaces in projectstatus? Try using Trim()

Make sure you use same case in the below If statement as in your data. Do you have two zeros or two O's in your data followed by 'I'?

MaxString(If(Trim(projectstatus)='I0046',projectstatus))

Not applicable
Author

Hi

No spaces in projectstatus, I tried using Trim (See below) . No zeros in front of the 'I'

So the code is still including the lines where I0046 is present but returning NULL as in a '-'

 

JESTPR:

LOAD
OBJNR as objectno,
STAT as projectstatus

FROM

(
qvd);

Inner Join(JESTPR)
Load * Where IsNull(Max);
Load objectno,MaxString(If(Trim(projectstatus='I0046'),projectstatus)) As Max, Minstring(projectstatus) as projectstatus Resident JESTPR Group By objectno;

jonathandienst
Partner - Champion III
Partner - Champion III

Something like this:

LOAD Distinct objectno As exclude

FROM (qvd)

WHERE projectstatus = 'I0046'

LOAD objectno,

  FormattedNumber1,

  projectstatus

FROM (qvd)

Where Not Exists(exclude, objectno);

DROP Field exclude;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
anbu1984
Master III
Master III

Can you attach sample QVD JESTPR.QVD

Not applicable
Author

Hi

I've attached 2 extracts. PRPS is the first file that has :

Field name POSID as Formattednumber1

Field name OBJNR as Objectnumber

JESTPR contains

OBJNR

STAT

So my goal is to exclude all of the I0046 records and for the remianing records leave 1 line of data

Thanks for your help so far

anbu1984
Master III
Master III

You have excluded objectnos from JESTPR and not from PRPS. That is the reason you are getting nulls. Inner join PRPS qvd as below

JESTPR:

LOAD
OBJNR as objectno,
STAT as projectstatus

FROM

(
qvd);

Inner Join(JESTPR)
Load * Where IsNull(Max);
Load objectno,MaxString(If(Trim(projectstatus='I0046'),projectstatus)) As Max,Minstring(projectstatus) as projectstatus Resident JESTPR Group By objectno;


Inner Join(JESTPR)

Load * From PRPS.qvd(qvd);