Load Script... help please

[#1]

Hello!

I have the next table:

Table1:

Concept     Value     Month     Year

Sales         10,000  Jan          2015

Sales          11,000 Feb          2015

Buy            5,000   Jan          2015

Buy            7000   Feb          2015

To this table i want CONCATENATE another Concept, named 'SumConcept', like this:

Table1:

Concept          Value     Month     Year

Sales              10,000  Jan          2015

Sales               11,000 Feb          2015

Buy                 5,000   Jan          2015

Buy                 7,000   Feb          2015

SumConcept     15,000  Jan     2015

SumConcept     18,000   Feb     2015

For example, the SumConcept is for month, SumConcept = Sales + Buy... for Jan is 15,000 (10,000 + 5,000) and for Feb is 18,000 (11,000 + 7,000)

I want do it in Load Script.

I have it:

Table_Temp

LOAD

    'Sales' as Concept,

    Value

    Month,

    Year

FROM db.Private

CONCATENATE

LOAD

    'Buy' as Concept,

    Value

    Month,

    Year

FROM db.Private

I want concatenate a line for each month where the value is the sum of value Sales + Buy.

Any idea?

[maxgro]

Table1:          // replace with your data

load * inline [

Concept,     Value,     Month   ,  Year

Sales   ,      10000,  Jan  ,        2015

Sales      ,    11000, Feb    ,      2015

Buy      ,      5000,   Jan   ,       2015

Buy       ,     7000,   Feb    ,      2015

] ;

load

Month, Year, 'SumConcept' as Concept, sum(Value) as Value

Resident Table1

where match(Concept, 'Sales', 'Buy')

group by Month,Year;

[#3]

Excellent!!!

Now, i need discount the value of the year-1 at the "SumConcept"

for example

i have:

Table1:

Concept     Value     Month     Year

Sales         10,000  Jan          2015

Sales          11,000 Feb          2015

Adjust          3000  Feb          2014

Buy            5,000   Jan          2015

Buy            7000   Feb          2015

at the Concept 'SumConcept' i need rest the year 2014 adjust like this:

for Feb: Sales + Buy - Adjust(2014) = 11,000 + 7000 - 3000 = 15,000

so, the new table will be:

Table1:

Concept     Value     Month     Year

Sales         10,000  Jan          2015

Sales          11,000 Feb          2015

Adjust        3,000    Feb        2014

Buy            5,000   Jan          2015

Buy            7000   Feb          2015

SumConcept   15,000    Jan    2015

SumConcept    15,000    Feb   2015

[maxgro]

Table1:

load * inline [

Concept,     Value,     Month   ,  Year

Sales   ,      10000,  Jan  ,        2015

Sales      ,    11000, Feb    ,      2015

Buy      ,      5000,   Jan   ,       2015

Buy       ,     7000,   Feb    ,      2015

Adjust     ,     3000 , Feb ,         2014

] ;

load

  Month,

  if(Concept='Adjust',Year+1,Year) as Year,

  'SumConcept' as Concept,

  sum(if(Concept='Adjust',-Value,Value)) as Value

Resident Table1

where match(Concept, 'Sales', 'Buy', 'Adjust')

group by Month,if(Concept='Adjust',Year+1,Year);

[#5]

Thank You so much !

another more... the last ^^

now, i need get a concept named 'OtherConcept'

this line is: ( ( Sales(of 2015) / Sales(of 2014) ) - 1 ) - 5

[maxgro]

there are no sales in 2014

please close this and open a new discussion