topic Re: Just one small thing on mapping table use in App Development

Just one small thing on mapping table use

valpassos — Sat, 16 Nov 2024 18:52:28 GMT

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

Re: Just one small thing on mapping table use

Wederson — Fri, 27 Mar 2020 17:42:46 GMT

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.

Re: Just one small thing on mapping table use

pablolabbe — Fri, 27 Mar 2020 17:46:12 GMT

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;

Re: Just one small thing on mapping table use

valpassos — Mon, 30 Mar 2020 09:35:09 GMT

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

Re: Just one small thing on mapping table use

pablolabbe — Mon, 30 Mar 2020 19:14:10 GMT

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.

Re: Just one small thing on mapping table use

valpassos — Fri, 03 Apr 2020 09:26:37 GMT

Hi @pablolabbe,

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

Thanks!