Innerkeep calculation

[ranibosch]

Good day, i've got the following sample data and is wondering if anyone can help me.

i have a list of prices on items active from certain dates as well as in different currencies.

i want to end up with a simple price list with the latest prices, including the foreign currency price converted to ZAR as at the latest exchange rate.

please refer to attached sample data.

[BrunPierre]

One way is to do something like this;

TempT1:
LOAD [Item no], 
     Currency, 
     [Starting date], 
     [Direct Unit Cost]
     
FROM [Purchase Price];

NoConcatenate
T1:
LOAD [Item no],
     Currency,
     FirstSortedValue([Direct Unit Cost],-[Starting date]) AS [Direct Unit Cost]

Resident TempT1
Group by [Item no],Currency
Order By [Item no],Currency;

TempXRate:
LOAD [Currency Code] AS Currency, 
     [Starting date], 
     [Relational Adjmt Exch Rate Amt]
FROM [Currency Exchange Rate];

Left Join(T1)
LOAD Currency,
LastValue([Relational Adjmt Exch Rate Amt]) AS [Relational Adjmt Exch Rate Amt]

Resident TempXRate
Group By Currency
Order by Currency;

[O/P Table]:
Load [Item no],
Num(IF(IsNull(Currency),[Direct Unit Cost],[Direct Unit Cost] * [Relational Adjmt Exch Rate Amt]),'#,##0.00') as [Item Cost]

Resident T1;

DROP Tables TempT1,TempXRate,T1;
     

[ranibosch]

Hi Peter,

Thank you for your response.

I've scripted like you suggested, it appears it ignores the blank currency fields. I wonder if it has to do with the "ISNULL" function?

I don't get the MAC001 result, only the ALM001 value.

I look forward to your feedback!

[BrunPierre]

@ranibosch  Has your requirement changed? Because I can assure you that based on your sample data the output from my code exactly matches your expected output as shown above.

Perhaps provide the actual script in your model.

[ranibosch]

hi Peter, i'll attach. thanks!

[BrunPierre]

Your code has IsNum in place of IsNull.

[ranibosch]

Apologies, i tried something and saved wrong version. please refer to attached with corrects formula.

[BrunPierre]

Has the source file been modified in anyway.

See attached, I only redirected to load from my source.

[ranibosch]

Hi Peter, no still the same source. I've made a table box with results. If I run script until just before the [O/P Table], the results are as follows:

If I include the [O/P Table] my results looks like this:

I've included the sample data again (for your reference).

[BrunPierre · Accepted solution]

I have modified the IF() in the attached.