Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Can someone help me with this?
I have an Excel file with a list of costs per line, according to the product (sale).
And in the script I have calculated that each of those lines, multiply that cost by the boxes (unit of sale) sold.
Right now, there is only one cost per reference (line), regardless of the year.
I want to add the year field, as the cost per product varies each year, and now applies the same cost, regardless of the selected year.
The current formula is:
ApplyMap ('Map_CosteCaja', Mid (% Key_Articulo, FindOneOf (% Key_Articulo, '123456789')), 0) * #Cajas as #CosteVentas
How can I add to this formula the option that if for example, if I select 2017 it uses a cost and if I select 2018 it uses a different cost?
For example:
Referencia | Año | Coste |
100001 | 2017 | 1,25 |
100001 | 2018 | 1,35 |
Thanks in advance!
Gaspar
Hi Gaspar,
The 2nd parameter of applymap uis the value to search, add a concatenated key using Year, and also include year in the first field on the mapping table Map_CosteCaja.
ApplyMap ('Map_CosteCaja', YearField&'#'&Mid (% Key_Articulo, FindOneOf (% Key_Articulo, '123456789')), 0) * #Cajas as #CosteVentas
Hi Gaspar,
The 2nd parameter of applymap uis the value to search, add a concatenated key using Year, and also include year in the first field on the mapping table Map_CosteCaja.
ApplyMap ('Map_CosteCaja', YearField&'#'&Mid (% Key_Articulo, FindOneOf (% Key_Articulo, '123456789')), 0) * #Cajas as #CosteVentas
Hi Ruben,
It works, I found the field in the Fact table and I have joined it following your advice YearField & '#' &
Many thanks!