Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

sathish_kumar
New Contributor II

Show latest value based on the table-Rolling Forecast

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

Capture.JPG

1 Solution

Accepted Solutions
MVP
MVP

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

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;

3 Replies
andrey_krylov
Valued Contributor

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

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;

MVP
MVP

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

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
New Contributor II

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

Thanks a lot Andrey