Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm trying to group some records together. I have the following
JESTPR:
LOAD
OBJNR As JestOBJNR,
STAT As ProjectStatus
FROM
(
The field STAT has multiple records to each record in OBJNR
STAT has a record called 'I0046' and if the record is equal to this then I want to call it 'OPEN' return it with the field OBJNR
If the field is not equal to 'I0046' then I want to group together the records and call them @CLOSED and return it with the field OBJNR
Andrew Thomas wrote:
THe DISTINCT logic worked to the point where it gave me 1 record for open and 1 for closed against the same OBJNR
That's correct. You did not specify that you want suppress the open if there are any closes...
This will prevent loading open if any close states exist.
JESTPR:
LOAD DISTINCT
OBJNR As JestOBJNR,
'Closed' As OpenState
FROM
WHERE STAT <> 'I0046';
Concatenate
LOAD DISTINCT
OBJNR As JestOBJNR,
'Open' As OpenState
FROM
WHERE STAT = 'I0046' AND Not(Exists(JESTPR));
WHERE STAT = 'I0046' AND Not(Exists(OBJNR ));
JESTPR:
LOAD DISTINCT
OBJNR As JestOBJNR,
'Closed' As OpenState
FROM
WHERE STAT <> 'I0046';
Concatenate
LOAD DISTINCT
OBJNR As JestOBJNR,
'Open' As OpenState
FROM
WHERE STAT = 'I0046' AND Not(Exists(JestOBJNR, OBJNR));
(Finally got that right!)
Thank you that worked