Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
gidon500
Creator II
Creator II

Data Load

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

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

Good challenge:

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

Capture.PNG.png

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;

View solution in original post

2 Replies
JonnyPoole
Employee
Employee

Good challenge:

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

Capture.PNG.png

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;

View solution in original post

gidon500
Creator II
Creator II
Author

Hi   Jonathan

thanks it great answer

have a reat evening

gidon