topic Re: Data Load in QlikView

Data Load

gidon500 — Mon, 15 Sep 2014 16:00:51 GMT

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

Re: Data Load

JonnyPoole — Mon, 15 Sep 2014 17:25:34 GMT

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;

Re: Data Load

gidon500 — Mon, 15 Sep 2014 18:03:50 GMT

Hi Jonathan

thanks it great answer

have a reat evening

gidon