Data reduction by Flag field

[Anonymous]

Hi Everyone,

I have a situation i was hoping somebody could help me with. I'm trying to reduce the below attached data set by only showing Serial #'s that do not have a flag of both 'Counted' and 'Inv. Bal.' in the OL-Inv.Flag field. I'm sure set analysis in a pivot/straight table or a left join using group by Serial # in the script will work... I am just having cloudy thoughts in coming up with a solution. Any help would be much appeciated!

To reiterate, I'm just after Serial #'s that have an indication of 'Inv. Bal.', but not 'Counted' in the OL-Inv.Flag field. They're all flagged as having 'Inv. Bal.', so we'll want to exclude the Serial # entirely from the data set if it has a flag of 'Counted'.

Thanks.

[thomaslg_wq · Accepted solution]

Hi,

you could add this script after yours:

Temp_Data_With_Counted: // know which serial not to take

load

distinct [Serial #] as Temp_Serial

resident DATA where OL-Inv.Flag='Counted';

noconcatenate // retake DATA without those serial

Data:

LOAD * resident DATA where not exists(Temp_Serial,[Serial #]);

Drop tables Temp_Data_With_Counted, DATA; // drop previous not used tables

Hope it helps,

Thomas

[Anonymous]

Sorry...........

Left Join (DATA)

LOAD

[Serial #],

sum(if([OL-Inv.Flag]='Counted',1,0)) as DUMBY_COUNT

Resident DATA

Group By [Serial #]

;

DATA_FINAL:

Load * Resident DATA Where DUMBY_COUNT = 0;

Drop Table DATA;

Drop Field DUMBY_COUNT;

[Anonymous]

Thanks a lot! don't know where my brain went this morning. I posted my own solution at about the same time.... lol