Join table

[gasparbenito]

Hi all,
I have a table (STOCK) with these data:

WERKS as Center,
LGORT as Warehouse,
MATNR as MaterialID,
BUDAT_MKPF as date_stock,
MTART as Type_Material,
MENGE as Quantity_Stock,

And on the other hand I have this table (VBAP), which is the orders:
MATNR as MaterialID,
KWMENG as Quantity_ ordered
ERDAT as ordered_date

The idea is to be able to see the stock and the consumption by material, by months or weeks or even years.
I've done a left join, but something I'm not doing well ...
Any advice?

Thanks!

[formosasol]

Hi,
Can you provide sample data.

Regards
Frank

[gasparbenito]

Atached file.

Hope it helps

Regards

[JordyWegman · Accepted solution]

Hi Gaspar,

What you can do is the following. Now your stock date is the leading date and all orders are summed based on article & date.

Table1:
Load
  MATNR &'|'& BUDAT_MKPF as %ArticleDate
  BUDAT_MKPF as %Date
  WERKS as Center,
  LGORT as Warehouse,
  MATNR as MaterialID,
  BUDAT_MKPF as date_stock,
  MTART as Type_Material,
  MENGE as Quantity_Stock,
From [Your Source];

Left Join(Table1)
Load
  *
;
Load
  MATNR &'|'& BUDAT as %ArticleDate
  MATNR as MaterialID,
  Sum(KWMENG) as Quantity_ ordered
  ERDAT as ordered_date
Group by MATNR, ERDAT
;

Calendar:
Load
  %Date,
  Week,
  Month,
  Period,
  Year
From [Your Source]
;

Jordy

Climber

[gasparbenito]

Hi Jordy, 

it seems it works with your advice.

Thanks!