topic Re: unable to easily exclude data in QlikView

unable to easily exclude data

Wed, 04 Mar 2015 14:41:21 GMT

I have a list of product codes I want to exclude from my report as this data is not relevant.

I have tried using a mapping load but that didn't work. I have tried linking in an extra table and that hasn't worked either. Even using the product code field as a list box and excluding the codes from there still does not amend the charts and graphs.

Ideally I would like to exclude the data in the script but that is proving harder than I thought.

I have attached the qvw file and a list of the products I am trying to exclude. They all have the same StockSANAL code of BEVA so I have tried to exclude that as can be seen in the qvw file. Excluding this data will make a massive difference to the Ave. TU per pick graph as these products are picked in the tens of thousands which is what causes the spike. So removing them would bring that bar more in line with the weeks around it.

Any help appreciated.

Steve

Re: unable to easily exclude data

maxgro — Wed, 04 Mar 2015 23:49:08 GMT

if I understand,

first rename StockCode (I suppose StockCode is ProductCode, ok?)

Products:

LOAD StockCode as ProductCode,

StockSANAL

FROM

[Products.qvd]

(qvd) where StockSANAL <> 'BEVA';

than add an exists when you load Guns to only load Product <>BEVA

Where (Type = 'Pick' or Type = 'NonFIFOPick')

and exists(ProductCode);

Re: unable to easily exclude data

Thu, 05 Mar 2015 08:04:32 GMT

Hi Massimo

Yes you are correct to suppose that StockCode is ProductCode. I forgot to do the rename. I added in the exists as well as you suggested but when I save and reload it doesn't change the data and doesn't remove the product codes relating to the 'BEVA' StockSANAL code.

Thanks Steve

Re: unable to easily exclude data

awhitfield — Thu, 05 Mar 2015 13:11:31 GMT

Hi Steve,

You are excluding the product codes relating to the 'BEVA' StockSANAL code from the Products table, but they are still in GUNS. See attached mini example,

Where Exists (ProductCode) ; seems to do the trick

Regards Andy

Re: unable to easily exclude data

maxgro — Thu, 05 Mar 2015 19:40:29 GMT

It should work because

Products:

LOAD StockCode as ProductCode,

StockSANAL

FROM [Products.qvd] (qvd)

where StockSANAL <> 'BEVA'; // here you filter BEVA

Guns:

LOAD LOGNAME,

DayStart(Date) as Date,

Time(TIME,'hh.mm.ss') as TIME,

......

left(MovementDescription,len(MovementDescription)-3),MovementDescription) as

SONumber

FROM

[Guns.qvd]

(qvd)

Where (Type = 'Pick' or Type = 'NonFIFOPick')

and exists(ProductCode); // and here you only load ProductCode not BEVA

// because of previous filter

Can you post the 2 qvd for checking?

Re: unable to easily exclude data

Fri, 06 Mar 2015 07:55:11 GMT

Hi Andrew

I was unable to open the larger spreadsheet as Excel says it's corrupt in some way.

I see what you are saying with the Where Exists (ProductCode) but that does not appear to be working in my instance.

I have attached the qvw again so you can see.

On the pick details sheet I have selected week 6 as this has a lot of BEVA product codes picked. Excluding these would make a clear difference to the graph on the previous sheet showing Ave. TU per pick.

Scrolling down the Pick Details table you can clearly see the products that come under the BEVA StockSANAL as they all start BV.

Steve.

Re: unable to easily exclude data

Fri, 06 Mar 2015 07:59:14 GMT

Hi Massimo

Attached is the updated qvw. As explained to Andrew when I isolate the week I know we picked the BEVA products they still exist in the list and as a result the graph does not change.

The Pick Details table shows the BEVA products still exist as the majority of those products start with the prefix BV.

Steve

Re: unable to easily exclude data

chrislofthouse — Fri, 06 Mar 2015 08:42:41 GMT

Try changing your where statement on the Guns table to this:

Where (Type = 'Pick' or Type = 'NonFIFOPick') and exists(ProductCode);

Re: unable to easily exclude data

awhitfield — Fri, 06 Mar 2015 08:46:59 GMT

Weird, those BV codes don't appear to have and StockANAL code associated with them, so, are they only in the GUNS .qvd, but NOT in the Products.QVD?

Could you post both QVDs?

Andy

Re: unable to easily exclude data

simenkg — Fri, 06 Mar 2015 08:56:43 GMT

You are trying to exclude products with BEVA?

in that case:

Products:

LOAD StockCode as TestField,

StockSANAL

FROM

[Products.qvd]

(qvd) where StockSANAL = 'BEVA';

Guns:

LOAD LOGNAME,

DayStart(Date) as Date,

Time(TIME,'hh.mm.ss') as TIME,

Shift,

'Pick' as Type,

Picks,

QTY as PickQty,

ProductCode,

Pallet,

GunsSource,

if(GunsSource = 'BarRed_TSL' and Type = 'NonFIFOPick', left(MovementDescription,len(MovementDescription)-3),MovementDescription) as SONumber

FROM

[..\Data\Guns.qvd]

(qvd)

Where not exists(TestField, ProductCode)

and (Type = 'Pick' or Type = 'NonFIFOPick');

Re: unable to easily exclude data

puttemans — Fri, 06 Mar 2015 08:59:17 GMT

Hi there,

Seems then that your product list is not exhaustive enough, and that there are more products in your guns table than in your products table. If all BEVA products start with BV, you could try to add the following at the end :

Where

Type = 'Pick' or Type = 'NonFIFOPick' and exists(ProductCode) and left(ProductCode, 2) <> 'BV';

Re: unable to easily exclude data

awhitfield — Fri, 06 Mar 2015 09:36:53 GMT

I was slight off here! Thinking again, the BV.. codes don't get excludde from the Products load because the don't have a BEVA StockANAL associated with them, create a table box with StockANAL and ProductCode and you'll see what I mean! They all come up as NULL StockANAL..

Andy

Re: unable to easily exclude data

Fri, 06 Mar 2015 09:48:31 GMT

Hi Andrew

Both QVDs attached. I have checked and the BV codes in the original spreadsheet attached have the BEVA StockSANAL associated with them.

Steve

Re: unable to easily exclude data

Fri, 06 Mar 2015 09:50:17 GMT

Thanks Johan but unfortunately not all products with the StockSANAL of BEVA start BV so that would only exclude a percentage of the stock codes.

Re: unable to easily exclude data

chrislofthouse — Fri, 06 Mar 2015 09:55:18 GMT

Is this not what you want?

Re: unable to easily exclude data

Fri, 06 Mar 2015 10:14:33 GMT

Hi Christopher

Unfortunately not, no. Upon opening this has messed up all of the graphs and text boxes that were reporting correctly.

This is all wrong now.

Steve

Re: unable to easily exclude data

chrislofthouse — Fri, 06 Mar 2015 10:22:50 GMT

Hi Steve,

Charts aside, has it excluded the product numbers you were trying to exclude?

As you will know it's important we get the data right first and then charts. It may that I've accidently altered a field name etc.

Chris

Re: unable to easily exclude data

Fri, 06 Mar 2015 10:27:17 GMT

Hi Chris

Sorry yes it has removed the codes.

Steve

Re: unable to easily exclude data

Fri, 06 Mar 2015 10:29:24 GMT

Hi Chris

Just been reading back through and I have missed this somehow. Just put this in and it's worked a treat. Thanks.

Excluded another couple of items that don't need to be counted and the report is looking good now.

Thanks for your help.

Steve.

Re: unable to easily exclude data

Fri, 06 Mar 2015 10:30:32 GMT

Thanks everyone for your help with this.