Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following set of data where an object number will have many different staus
objectno | FormattedNumber1 | projectstatus |
PR00006588 | 02.000409.01.01 | E0001 |
PR00006588 | 02.000409.01.01 | I0001 |
PR00006588 | 02.000409.01.01 | I0002 |
PR00006588 | 02.000409.01.01 | I0045 |
PR00006588 | 02.000409.01.01 | I0046 |
PR00006588 | 02.000409.01.01 | I0118 |
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
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))
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
(
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;
Something like this:
LOAD Distinct objectno As exclude
FROM
WHERE projectstatus = 'I0046'
LOAD objectno,
FormattedNumber1,
projectstatus
FROM
Where Not Exists(exclude, objectno);
DROP Field exclude;
Can you attach sample QVD JESTPR.QVD
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
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
(
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);