Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
Hope you can help me. I have data from SQL plus ficticious data to create a forcasted analysis on future consumption.
Here is my code (Up to 1000 Employees and 10 departments):
Table1:
SELECT Employee, Consumption, Department, Month
from SQLTableName1;
tempmaxmonth:
load
max(Month) as maxmonth
resident Table1;
let vmaxmonth = Peek(‘maxmonth’, 0, tempmaxmonth);
let vforecastmonth = $(vmaxmonth) + 3;
drop Table tempmaxmonth;
Table1:
load * ;
Concatenate
tempmonth:
Load $(vmaxmonth)+IterNo() as Month
AutoGenerate(1)
While $(vmaxmonth)+IterNo()<=$(vforecastmonth) and $(vmaxmonth)+IterNo() < 13
This is my result:
This is what I would like:
Question is that I don't get the point on how to finally link real SQL data with future months.
Many TKS!
Your concatenate is too early because at this moment you have just generated the rest months of the year but there aren't any data from your employees. I think I would do it in this way:
Table1: select ...
tempmaxmonth: load ...
tempmonth: ...
join(tempmonth)
load distinct Employee, Department, 0 as Consumption resident Table1;
concatenate(Table1)
load * resident tempmonth;
- Marcus
Your concatenate is too early because at this moment you have just generated the rest months of the year but there aren't any data from your employees. I think I would do it in this way:
Table1: select ...
tempmaxmonth: load ...
tempmonth: ...
join(tempmonth)
load distinct Employee, Department, 0 as Consumption resident Table1;
concatenate(Table1)
load * resident tempmonth;
- Marcus
Hi Marcus, it did work perfectly!!
Many TKS!!