Data Load

[gidon500]

Hi

I need help

in our system the sales manager create sales target for each Agent

he add to the table the agent name , year , frommonth Tomonth and the sales target per Agent

he can use any combination he wants

i need to creat a table with every month of the year  .

enclosed is an excel file

tha blue is the data I get

the green is the result

So I can have the  sales target for each month in the year in the table

Thanks

gidon

[JonnyPoole · Accepted solution]

Good challenge:

Here is my sample solution with load script where all the work is done:

temp:

LOAD

  Agent,

    Year,

    FromMonth,

    ToMonth

FROM

(ooxml, embedded labels, table is data);

for i = 1 to 12

  for j = $(i) to 12

  for k = $(i) to $(j)

  Bridge:

  load

  $(i) as FromMonth,

  $(j) as ToMonth,

  $(k) as  Month

  AutoGenerate 1;

  next k;

  next j;

next i;

left join (temp)

load

  FromMonth,

  Month,

  ToMonth

Resident Bridge;

drop table Bridge;

Data:

LOAD

  Agent,

    Year,

    FromMonth,

    ToMonth,

    SalesPerMonth

FROM

(ooxml, embedded labels, table is data);

join (Data)

load

  *

Resident temp;

drop table temp;

[gidon500]

Hi   Jonathan

thanks it great answer

have a reat evening

gidon