topic Re: Merge data from SQL query and resident Table in App Development

Merge data from SQL query and resident Table

oscarvictory — Wed, 06 Oct 2021 14:56:52 GMT

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!

Re: Merge data from SQL query and resident Table

marcus_sommer — Wed, 06 Oct 2021 15:19:48 GMT

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

Re: Merge data from SQL query and resident Table

oscarvictory — Wed, 06 Oct 2021 15:59:46 GMT

Hi Marcus, it did work perfectly!!

Many TKS!!