Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I have modified the IF() in the attached.
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;
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!
@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.
hi Peter, i'll attach. thanks!
Your code has IsNum in place of IsNull.
Apologies, i tried something and saved wrong version. please refer to attached with corrects formula.
Has the source file been modified in anyway.
See attached, I only redirected to load from my source.
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).
I have modified the IF() in the attached.