How to pull specific deminsion for a table of multiple deminsions.

[wschefter]

Hi all;

I am having what I believe is a simple problem to solve but I am missing the solution.  Here is the situation:

I am trying to pull reorder point for specific plants and specific part numbers.  This is for a table.  I am pulling usage for 36 months for Plant A for the specific PNs.  Now I am trying to pull in the ROP into the table for the specific PN and plant.  Here is the data:

Plant	PN	ROP
Plant A	123456	10
Plant B	123456	2
Plant C	123456	3

For some reason I am just not seeing how to do it and I know it is a simple answer.  Any advice would be greatly appreciate.

Thanks.

Will

[petter]

You will need to have a smaller table (compared to the larger usage table for the plants) that contains the reorder points of all plants and part numbers. You probably have them in your source system - if not you just have to create either an inline table or an Excel workbook with a worksheet that contains them.

PART_INFO:

LOAD

  Plant & '-' & PN AS %PlantPart,  // Key to connect it with the USAGE table

  ROP

FROM

    .....;

USAGE:

LOAD

    Plant & '-' & PN AS %PlantPart,  // Key to connect it with the PART_INFO table

    Plant,

    PN,

    Date,

    Usage,

    ....

FROM

  ....;

You will have two tables that are associated by Plant and PartNumber PN and this will give you the option of referring to the ROP just as if it was a column in the USAGE table.

An alternative if you don't want the PART_INFO table but only have the ROP added as an extra field into the USAGE table is to perform a join:

PART_INFO:

LOAD

  Plant,

  PN,

  ROP

FROM

    .....;

RIGHT JOIN    // will do a NATURAL join using the common fields to match between the tables

USAGE:

LOAD

    Plant,

    PN,

    Date,

    Usage,

    ....

FROM

  ....;

[ogautier62]

which one ?   😉

and how is it calculated ?

regards

[wschefter]

Hi Peter, thank you.  I have to really learn to write everything out with the question...I really gave about 75% of the needed information.

This data is coming from Excel files all ready.  Many of my sheets have associations between Plant and PN (in fact, I think all of them do).

In this case the data is coming from sheet Overall Usage (Plant, PN, usage, dates on this file) and sheet ROP (plant, PN, ROP (reorder point ).  So I am trying to match the PN/plant from both Overall Usage and ROP to the actual ROP for that plant/PN in the ROP file.  That's where I keep getting lost.

Does that make sense?

Thanks.