Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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;
Thanks a lot! don't know where my brain went this morning. I posted my own solution at about the same time.... lol