topic Re: Data reduction by Flag field in QlikView

Data reduction by Flag field

Anonymous — Fri, 20 Nov 2015 15:45:03 GMT

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.

Re: Data reduction by Flag field

thomaslg_wq — Fri, 20 Nov 2015 16:08:18 GMT

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

Re: Data reduction by Flag field

Anonymous — Fri, 20 Nov 2015 16:08:52 GMT

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;

Re: Data reduction by Flag field

Anonymous — Fri, 20 Nov 2015 16:12:22 GMT

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