remove materials with QTY = 0

[#1]

Hi,

I have an application which exposes YTD, MP and PY prices and volumes for each material.

The request is to remove all materials which have the quantity equal to zero for country and for the year I select .

I’ve tried but it seems had no effect.

My script is:

COUNTRY_FILTERS:
NoConcatenate
LOAD distinct
COUNTRY_ID as COUNTRY_ID_FILTER
FROM
[$(vPathQVD1)ST_MACRO_REGION.qvd](qvd);

MATERIAL_FILTERS:
NoConcatenate
LOAD distinct MAT_ID_MATERIAL as MAT_ID_MATERIAL_FILTER
FROM $(vPathQVD1)ST_DIM_MATERIAL_MOVALL.qvd
(qvd);

PURCHASES_TEMP:
NOCONCATENATE
LOAD
COUNTRY_ID,
DATE_ID,
MAT_ID_MATERIAL,
PURCHASING_ORG_ID,
STORAGE_LOCATION_ID,
S_SUPPLIER_ID,
BU_PURCH_ID,
CR_ID_CURRENCY,
YEAR_MONTH,
left(YEAR_MONTH, 4) as YEAR_CODE,
TOTAL_QUANTITY,
VOLUME,
LOCAL_ADDED_COST,
LOCAL_TOTAL_PRICE,
EUR_ADDED_COST,
EUR_TOTAL_PRICE,
USD_ADDED_COST,
USD_TOTAL_PRICE
from
[$(vPathQVD1)ST_F_PURCHASES_ACQ_RM.qvd](qvd)
WHERE
left(DATE_ID, 6) <= year(now())&''&Num(month(now()), '00')-1
AND exists(COUNTRY_ID_FILTER,COUNTRY_ID)
AND exists(MAT_ID_MATERIAL_FILTER,MAT_ID_MATERIAL) ;

CONCATENATE  (PURCHASES_TEMP)
LOAD *
where  exists(COUNTRY_ID_FILTER,COUNTRY_ID)
AND exists(MAT_ID_MATERIAL_FILTER,MAT_ID_MATERIAL);
LOAD
COUNTRY_ID,
BU_PURCH_ID,
STORAGE_LOCATION_ID,
MAT_ID_MATERIAL,
CR_ID_CURRENCY,
DATE_ID + 100*(iterno()-1) as DATE_ID,
left(DATE_ID,4) as YEAR_CODE,
MP_LOCAL_TOTAL_PRICE,
MP_EUR_TOTAL_PRICE,
MP_USD_TOTAL_PRICE,
MP_VOLUME
from
[$(vPathQVD1)ST_F_MP_PURCHASES_RM.qvd](qvd)
while iterno() <=12;

left join(PURCHASES_TEMP)
LOAD DISTINCT

AT_ID_MATERIAL,
MG_CODE_MAT_GROUP_LEV1
FROM
[$(vPathQVD1)ST_DIM_MATERIAL_MOVALL.qvd](qvd);
DROP TABLE COUNTRY_FILTERS;
DROP TABLE
MATERIAL_FILTERS;

In order to remove those materials with the quantity equal
to zero, I’ve created the following script:

 
PURCHASES_QTY_TEMP:
NoConcatenate
LOAD
MAT_ID_MATERIAL,
COUNTRY_ID,
YEAR_CODE,

     SUM(TOTAL_QUANTITY) AS TOTAL_QUANTITY
RESIDENT PURCHASES_TEMP
GROUP BY  MAT_ID_MATERIAL,
          COUNTRY_ID,
          YEAR_CODE;

PURCHASES_QTY:
NoConcatenate
LOAD
     MAT_ID_MATERIAL as MAT_QTY_NOT_ZERO,
     COUNTRY_ID,
     YEAR_CODE
RESIDENT
PURCHASES_QTY_TEMP
WHERE TOTAL_QUANTITY <> 0;
DROP TABLE
PURCHASES_QTY_TEMP;

PURCHASES:
NoConcatenate
LOAD
     DATE_ID,
     MAT_ID_MATERIAL,
     COUNTRY_ID,
     CR_ID_CURRENCY,
     STORAGE_LOCATION_ID,
     BU_PURCH_ID,
     PURCHASING_ORG_ID,
     S_SUPPLIER_ID,
     YEAR_MONTH,
     COUNTRY_ID&'|'&MG_CODE_MAT_GROUP_LEV1&'|'&YEAR_CODE AS KEY_PERCENT,
     TOTAL_QUANTITY,
     VOLUME,
     LOCAL_ADDED_COST,
     LOCAL_TOTAL_PRICE,
     EUR_ADDED_COST,
     EUR_TOTAL_PRICE,
     USD_ADDED_COST,
     USD_TOTAL_PRICE,
     MP_LOCAL_TOTAL_PRICE,
     MP_EUR_TOTAL_PRICE,
     MP_USD_TOTAL_PRICE,
     MP_VOLUME
RESIDENT PURCHASES_TEMP
WHERE EXISTS(MAT_QTY_NOT_ZERO, MAT_ID_MATERIAL);

  But I can still see materials with quantity  = 0.

  Do you have any idea how to solve
this problem?

thanks

[Nicole-Smith]

The sum may be returning null instead of 0, so maybe try:

WHERE TOTAL_QUANTITY > 0

or

WHERE isnum(TOTAL_QUANTITY) and TOTAL_QUANTITY > 0

[Gysbert_Wassenaar]

The request is to remove all materials which have the quantity equal to zero for country and for the year I select

WHERE EXISTS(MAT_QTY_NOT_ZERO, MAT_ID_MATERIAL)

You only check for the material, disregarding country and year. So if a material has a quantity in any year or country it will be included, because the where clause doesn't consider year or country. You could try creating a key from material, country and year: something like autonumber(MAT_ID_MATERIAL&COUNTRY_ID&YEAR_CODE,'MCY') as MCY_KEY. Then use the key in the where clause instead to filter out the purchase records that match the key.

WHERE EXISTS(MCY_KEY, autonumber(MAT_ID_MATERIAL&COUNTRY&YEAR_CODE,'MCY'))