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
T1:
LOAD * Inline [
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
PR00006589,02.000409.01.01,I0118 ];
Inner Join(T1)
Load * Where IsNull(Max);
Load objectno,MaxString(If(projectstatus='I0046',projectstatus)) As Max Resident T1 Group By objectno;
Thanks
The data is coming from a QVD file and therfore all my data will be loaded for multiple object numbers, what I need is the code to exclulde object numbers where the project status = I0046.
If you run the above script, Objectno PR00006588 is excluded since it has project status = I0046 and PR00006589 is retained
Thanks...I've managed to exclude the I0046 records but I only want to see one line for the rest of my data. How can I elminate the duplicate lines
Company Code | Plant | Projectno. | WBS Element | WBS Desc. | Market | Project Manager | Milestone | Milestone Desc. | projectstatus | Actual Date | Scheduled Date |
US05 | USP2 | 02.000409 | 02.000409.90.00 | BASIN ELECTRIC Warranty Phase | 251 | Hamilton, Phil CO | 13450 | Start of System Warranty | E0001 | 2012-07-31 | 2012-07-31 |
US05 | USP2 | 02.000409 | 02.000409.90.00 | BASIN ELECTRIC Warranty Phase | 251 | Hamilton, Phil CO | 13450 | Start of System Warranty | I0001 | 2012-07-31 | 2012-07-31 |
US05 | USP2 | 02.000409 | 02.000409.90.00 | BASIN ELECTRIC Warranty Phase | 251 | Hamilton, Phil CO | 13450 | Start of System Warranty | I0002 | 2012-07-31 | 2012-07-31 |
US05 | USP2 | 02.000409 | 02.000409.90.00 | BASIN ELECTRIC Warranty Phase | 251 | Hamilton, Phil CO | 13450 | Start of System Warranty | I0028 | 2012-07-31 | 2012-07-31 |
US05 | USP2 | 02.000409 | 02.000409.90.00 | BASIN ELECTRIC Warranty Phase | 251 | Hamilton, Phil CO | 13450 | Start of System Warranty | I0118 | 2012-07-31 | 2012-07-31 |
Which row you want to retain? Any row is fine out of duplicates?
Yes... after removing the status of I0046 I just want to see one project status record, but I can't do it based on the maining codes as there could be many different codes in the data. I just need to aggregate the data down to one line
Another way with not exists
Chris
T1:
LOAD * Inline [
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
PR00006589,02.000409.01.01,I0118
PR00006589,02.000409.01.01,I0002 ];
Inner Join(T1)
Load * Where IsNull(Max);
Load objectno,MaxString(If(projectstatus='I0046',projectstatus)) As Max,MinString(projectstatus) As projectstatus Resident T1 Group By objectno;
Hi
Still having an issue with this code. The data is loading ok but where the status = I0046 it is not excluding it from the data but returning NULL. Where I0046 is not present then it seems to be working and only returning one record.