Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

anbu1984
Honored Contributor III

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

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))

Highlighted
Not applicable

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

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;

MVP
MVP

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

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;

anbu1984
Honored Contributor III

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

Can you attach sample QVD JESTPR.QVD

Not applicable

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

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
Honored Contributor III

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

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);