Just one small thing on mapping table use

[valpassos]

Hi community,

This will be for an easy and quick wedge.

A mapping table always returns one and one only value, right.

MAPPING LOAD
%KEY_PRODUCT,
MIN(Order_Date)
FROM Table
GROUP BY %KEY_PRODUCT;

So, this mapping table will return the MIN(Order_Date) for the product.

But if I have multiple Order_Dates, varying for each version of the product, a mapping table no longer makes sense to use, right? Even if I GROUP BY the version of the product?

Is this true under any circunstance? 

If so, what should I do, then - a LEFT JOIN?

Thanks!

Lisa

[Wederson]

The mapping table returns one value for each key value. In yout Example, the mapply will return the minium order_date for each KEY_PRODUCT.

[pablolabbe · Accepted solution]

You have two options:

Use a left join using %KEY_PRODUCT and VERSION (field with version of the product) as matching fields.

Or you can create a mapping table concatenating product key and product version fields in one single field and use it as the mapping column:

MAPPING LOAD
%KEY_PRODUCT&'|'&VERSION,
MIN(Order_Date)
FROM Table
GROUP BY %KEY_PRODUCT,VERSION;

[valpassos]

Hi @pablolabbe ,

Thank you!

Immediately after I posted the question I realized that using a composite key, as you describe, on the mapping table maybe could also make sense 🤔 And it sure beats the LEFT JOIN in terms of performance, right?

I haven't acted upon that approach yet on my model, but using this composite key, can the Version field then be used as a filter on the front-end, so I can vary the order date?

Thanks once again!,

Lisa

[pablolabbe]

Hi Lisa,

  I'm not sure on what you are saying  "so I can so I can vary the order date?" . It depends on your data model. The Version field must exists in the table which you are applying the applymap function.

[valpassos]

Hi @pablolabbe,

Forget about it -- that question didn't make sense 😊

Thanks!