Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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;
Thanks a lot Andrey