if logic with group by in load script for field

[matthewp]

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.

[matthewp]

Doesnt work. doesnt define Acheck by group only by the line

[matthewp]

both give me an invalid expression error

[sunny_talwar]

I got this

I see an extra MISSING for Luke

[matthewp]

Getting an invalid expression error on reload

[sunny_talwar]

When you are running the application i attached, it is giving error? Really? I just reloaded it and attached it. Can you share what error did you get?

[matthewp]

Invalid expression

Left Join (resulttable)

LOAD IDField,

  If(Only(Checknumber) = 'NULL', 'Missing', 'OK') as ACheck

Resident resulttable

Where Part <> 'PALD'

Group By IDField;

[sunny_talwar]

Would you be able to generate a logfile and share?

[Kushal_Chawda]

try this

Table:

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,

           Count(IDField) as CntID

           count(if(Checknumber='NULL',IDField)) as CntNull,

           Count(if(Part='PALD',IDField)) as CntPALD

Resident Table

Group By IDField;

Final:

LOAD *,

          if((CntID=CntNull) or CntPALD>0,'Missing','Ok') as Acheck

Resident Table;

Drop table Table;

Drop fields CntID,CntNull,CntPALD;

[matthewp]

this just makes everything missing

[sunny_talwar]

Can you try re-running the attached application and send me the log file? I have already enabled the generation of the log file