unable to easily exclude data

[#1]

Hi

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

[maxgro]

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);

[#3]

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

[awhitfield]

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

[maxgro]

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?

[#6]

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.

[#7]

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

[chrislofthouse · Accepted solution]

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

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

[awhitfield]

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

[simenkg]

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');