topic Re: Show latest value based on the table-Rolling Forecast in QlikView

Show latest value based on the table-Rolling Forecast

sathish_kumar — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to show the latest forecast value using qlikview. I will be getting forecast for everyday but i want to map latest forecast date with the actual using dates

The logic is, the forecast should be mapped based on the forecast date and actual date in the load script?

Can any one help me in this regard.

stalwar1‌

Re: Show latest value based on the table-Rolling Forecast

andrey_krylov — Sun, 21 Oct 2018 16:55:12 GMT

Hi. Try this logic

Table:

LOAD Date,Product,Actual,

[Forecast

20-10-2018],

[Forecast

21-10-2018],

[Forecast

22-10-2018],

[Forecast

23-10-2018]

FROM Rolling.xlsx (ooxml, embedded labels, table is Date);

FOR vCounter = 0 TO NoOfFields('Table') - 1

vCurrentField = FieldName($(vCounter), 'Table');

IF WildMatch(vCurrentField, 'Forecast*') THEN

ForecastFields: LOAD '$(vCurrentField)' as FieldName, Date(Trim(SubField('$(vCurrentField)', Chr(13), 2))) as Date AUTOGENERATE 1;

ENDIF

TempTable: LOAD 'If(Len(Trim([' & Concat(FieldName & '])) > 0, [' & FieldName & ']', ', If(Len(Trim([', -Date) as ForecastFields Resident ForecastFields;

vString = Peek('ForecastFields', 0) & Repeat(')', NoOfRows('ForecastFields')) & 'as [Forecast]';

Table2: LOAD *, $(vString) Resident Table; DROP Tables Table, TempTable, ForecastFields;

Re: Show latest value based on the table-Rolling Forecast

swuehl — Sun, 21 Oct 2018 20:36:57 GMT

Or maybe something like this:

Forecasts:

LOAD Date,Product,Actual,

[Forecast

20-10-2018],

[Forecast

21-10-2018],

[Forecast

22-10-2018],

[Forecast

23-10-2018]

FROM

Rolling.xlsx

(ooxml, embedded labels, table is Date);

TMP:

CROSSTABLE (ForecastDate, Value,3)

LOAD *

Resident Forecasts;

LEFT JOIN (Forecasts)

LOAD Date,Product,

FirstSortedValue(Value, -Right(ForecastDate,10)) as [Forecast Desired Output]

Resident TMP

Group BY Date,Product;

DROP TABLE TMP;

Re: Show latest value based on the table-Rolling Forecast

sathish_kumar — Mon, 22 Oct 2018 09:23:35 GMT

Thanks a lot Andrey