topic Re: Innerkeep calculation in QlikView

Innerkeep calculation

ranibosch — Fri, 02 Sep 2022 12:36:52 GMT

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.

Re: Innerkeep calculation

BrunPierre — Fri, 02 Sep 2022 14:25:44 GMT

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;

Re: Innerkeep calculation

ranibosch — Mon, 05 Sep 2022 12:31:51 GMT

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!

Re: Innerkeep calculation

BrunPierre — Mon, 05 Sep 2022 19:23:22 GMT

@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.

Re: Innerkeep calculation

ranibosch — Tue, 06 Sep 2022 09:49:29 GMT

hi Peter, i'll attach. thanks!

Re: Innerkeep calculation

BrunPierre — Tue, 06 Sep 2022 15:51:40 GMT

Your code has IsNum in place of IsNull.

Re: Innerkeep calculation

ranibosch — Tue, 06 Sep 2022 12:55:28 GMT

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

Re: Innerkeep calculation

BrunPierre — Tue, 06 Sep 2022 20:54:01 GMT

Has the source file been modified in anyway.

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

Re: Innerkeep calculation

ranibosch — Wed, 07 Sep 2022 06:22:39 GMT

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).

Re: Innerkeep calculation

BrunPierre — Wed, 07 Sep 2022 09:45:37 GMT

I have modified the IF() in the attached.