Show latest value based on the table-Rolling Forecast

[sathish_kumar]

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‌

[andrey_krylov]

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

NEXT

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;

[swuehl · Accepted solution]

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;

[sathish_kumar]

Thanks a lot Andrey