Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to make a field called ACheck that will output either Missing or Ok.
However I'm not sure how to get the logic to apply to a whole group to return the correct data.
Sample Data:
IDField Description Checknumber Part
A1001 John ABC 656 A CALD
A1001 John ABC 756 D CALD
A1001 John ABC 856 T CALD
A1002 Mark NULL CALD
A1002 Mark NULL CALD
A1002 Mark NULL CALD
A1003 Luke ABC 482 A CALD
A1003 Luke NULL CALD
A1003 Luke ABC 912 T CALD
A1004 Matt NULL CALD
A1004 Matt ABC 777 H PALD
A1004 Matt NULL CALD
The logic needs to be as follows:
IF the Checknumber IS NULL for ALL IDField (NOT just NULL in some)
AND ignore where Part = PALD
Expected Output:
IDField Description Checknumber Part ACheck
A1001 John ABC 656 A CALD Ok
A1001 John ABC 756 D CALD Ok
A1001 John ABC 856 T CALD Ok
A1002 Mark NULL CALD Missing
A1002 Mark NULL CALD Missing
A1002 Mark NULL CALD Missing
A1003 Luke ABC 482 A CALD Ok
A1003 Luke NULL CALD Ok
A1003 Luke ABC 912 T CALD Ok
A1004 Matt NULL CALD Missing
A1004 Matt ABC 777 H PALD Missing
A1004 Matt NULL CALD Missing
As you can see even though the A1004 group aren't all NULL's they are still getting classed as missing because i want to ignore PALD.
Try this:
Table:
LOAD RowNo() as Key,
*;
LOAD * Inline [
IDField, Description, Checknumber, Part
A1001, John, ABC 656 A, CALD
A1001, John, ABC 756 D, CALD
A1001, John, ABC 856 T, CALD
A1002, Mark, NULL, CALD
A1002, Mark, NULL, CALD
A1002, Mark, NULL, CALD
A1003, Luke, ABC 482 A, CALD
A1003, Luke, NULL, CALD
A1003, Luke, ABC 912 T, CALD
A1004, Matt, NULL, CALD
A1004, Matt, ABC 777 H, PALD
A1004, Matt, NULL, CALD
];
Left Join (Table)
LOAD IDField,
If(Only(Checknumber) = 'NULL', 'Missing', 'OK') as ACheck
Resident Table
Where Part <> 'PALD'
Group By IDField;
Can the Where Part <> 'PALD' be integrated into the if, purely because i dont want them removed from the load as they are used elsewhere in the document
Table:
LOAD *,
if(Checknumber='NULL' or Part='PALD','MISSING',
if(Checknumber<>'NULL','Ok')) ACheck
LOAD * Inline [
IDField, Description, Checknumber, Part
A1001, John, ABC 656 A, CALD
A1001, John, ABC 756 D, CALD
A1001, John, ABC 856 T, CALD
A1002, Mark, NULL, CALD
A1002, Mark, NULL, CALD
A1002, Mark, NULL, CALD
A1003, Luke, ABC 482 A, CALD
A1003, Luke, NULL, CALD
A1003, Luke, ABC 912 T, CALD
A1004, Matt, NULL, CALD
A1004, Matt, ABC 777 H, PALD
A1004, Matt, NULL, CALD
];
Update : ')' was missing in If
See if the below works:
Replace below script:
Left Join (Table)
LOAD IDField,
If(Only(Checknumber) = 'NULL', 'Missing', 'OK') as ACheck
Resident Table
Where Part <> 'PALD'
Group By IDField;
Like below:
Left Join (Table)
LOAD IDField,
If(Only(Checknumber) = 'NULL' And Part <> 'PALD' , 'Missing', 'OK') as ACheck
Resident Table
Group By IDField;
Have you looked at the document? The rows with PALD are still available. They are not getting removed
Do we really need group by here stalwar1 ?
How will we do this without Group By?
Can you try my if condition and check?
Sure