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
Hi,
then just modify your LOAD with a new calculated field, like
>> LOAD OBJNR, STAT, IF(STAT='I0046','OPEN', '@CLOSED') as Status ... <<
HTH
Are those the only codes involved?
JESTPR:
LOAD
OBJNR As JestOBJNR,
STAT As ProjectStatus,
If(STAT = 'I0046', 'Open', 'Closed') As OpenState
FROM
(qvd);
If there are more codes to interpret, you might want to use a mapping table.
Thanks
But the issue is that there are many different codes that will define CLOSED status and I want to group them together to display CLOSED against one OBJNR
e,g
So I need to group all of the projectStatus codes to show 1 record of CLOSED against OBJNR PR0000002
JestOBJNR | ProjectStatus | Status |
PR00000002 | E0001 | @CLOSED |
PR00000002 | I0001 | @CLOSED |
PR00000002 | I0002 | @CLOSED |
PR00000002 | I0005 | @CLOSED |
PR00000002 | I0028 | @CLOSED |
PR00000002 | I0042 | @CLOSED |
PR00000002 | I0045 | @CLOSED |
PR00000002 | I0082 | @CLOSED |
PR00000002 | I0295 | @CLOSED |
PR00000002 | I0460 | @CLOSED |
In the front end, create a table with JestOBJNR and Status as dimensions, and wherever metrics you require in the expressions. The two dimension values will only be displayed once.
If there is no other data in your source table, then do this:
JESTPR:
LOAD DISTINCT
OBJNR As JestOBJNR,
If(STAT = 'I0046', 'Open', 'Closed') As OpenState
FROM
(qvd);
It seems like you want to create per record per OBJNR per status:
If so, you can accomplish this either in the front end or in the load script with another preceding load statement:
Hi
No that doesn't work
I need to end up with an open or closed status against each OBJNR
if STAT = 'I0046' then the record should show Closed
However if the record isnt 'I0046' there can be many other codes that represent OPEN status and I need to group these together against 1 OBJNR
Well that is exactly what that script does - the DISTINCT will collapse all the Closed lines into a single line.
Yes understand but see the example below.
I want to be able to show 1 JestOBJNR record that has an open or closed status
Closed status will always be 'I0046' but there will be other status recorded against the OBJNR
if 'I0046@ isnt present then the status is closed but I only want one record
THe DISTINCT logic worked to the point where it gave me 1 record for open and 1 for closed against the same OBJNR
JestOBJNR | STAT |
PR00000001 | I0001 |
PR00000001 | I0042 |
PR00000002 | E0001 |
PR00000002 | I0001 |
PR00000002 | I0002 |
PR00000002 | I0005 |
PR00000002 | I0028 |
PR00000002 | I0042 |
PR00000002 | I0045 |
PR00000002 | I0082 |
PR00000002 | I0295 |
PR00000002 | I0460 |
PR00000003 | I0001 |
PR00000003 | I0002 |
PR00000003 | I0045 |
PR00000003 | I0118 |
PR00000004 | I0001 |
PR00000004 | I0002 |
PR00000004 | I0005 |
PR00000004 | I0045 |
PR00000004 | I0118 |
PR00000005 | I0001 |
PR00000005 | I0002 |
PR00000005 | I0005 |
PR00000005 | I0045 |
PR00000005 | I0118 |
PR00000006 | I0001 |
PR00000006 | I0002 |
PR00000006 | I0005 |
PR00000006 | I0045 |
PR00000006 | I0118 |
PR00000007 | I0001 |
PR00000007 | I0002 |
PR00000007 | I0045 |
PR00000007 | I0118 |
PR00000008 | E0001 |
PR00000008 | I0001 |
PR00000008 | I0002 |
PR00000008 | I0028 |
PR00000008 | I0045 |
PR00000009 | I0001 |
PR00000009 | I0042 |
PR00000010 | E0001 |
PR00000010 | I0001 |
PR00000010 | I0002 |
PR00000010 | I0005 |
PR00000010 | I0028 |
PR00000010 | I0042 |
PR00000010 | I0045 |
PR00000010 | I0082 |
PR00000010 | I0295 |
PR00000010 | I0460 |
PR00000011 | I0001 |
PR00000011 | I0002 |
PR00000011 | I0045 |
PR00000011 | I0118 |
PR00000012 | I0001 |
PR00000012 | I0002 |
PR00000012 | I0005 |
PR00000012 | I0045 |
PR00000012 | I0118 |
PR00000013 | I0001 |
PR00000013 | I0002 |
PR00000013 | I0005 |
PR00000013 | I0045 |
PR00000013 | I0118 |
PR00000014 | I0001 |
PR00000014 | I0002 |
PR00000014 | I0045 |
PR00000014 | I0118 |
PR00000015 | I0001 |
PR00000015 | I0002 |
PR00000015 | I0045 |
PR00000015 | I0118 |
PR00000016 | I0001 |
PR00000016 | I0002 |
PR00000016 | I0005 |
PR00000016 | I0028 |
PR00000016 | I0042 |
PR00000016 | I0082 |
PR00000016 | I0118 |
PR00000016 | I0295 |
PR00000017 | E0001 |
PR00000017 | I0001 |
PR00000017 | I0028 |
PR00000017 | I0118 |
PR00000018 | I0001 |
PR00000018 | I0002 |
PR00000018 | I0005 |
PR00000018 | I0042 |
PR00000018 | I0045 |
PR00000018 | I0067 |
PR00000018 | I0093 |
PR00000019 | E0001 |
PR00000019 | E0005 |
PR00000019 | I0001 |
PR00000019 | I0002 |
PR00000019 | I0005 |
PR00000019 | I0028 |
PR00000019 | I0042 |
PR00000019 | I0045 |
PR00000019 | I0046 |
PR00000019 | I0082 |
PR00000019 | I0295 |
PR00000019 | I0460 |
something like this?
JESTPR:
LOAD
OBJNR As JestOBJNR,
STAT As ProjectStatus,
If(STAT = 'I0046', 'Open', 'Closed') As OpenState
FROM
(qvd);
NoConcatenate
JESTPR1:
LOAD OBJNR As JestOBJNR,Concat(ProjectStatus,',') as TotalProjectStatus,OpenState
Resident JESTPR
group by JestOBJNR,OpenState;
drop table JESTPR;