topic Re: Load Script... help please in QlikView

Load Script... help please

Tue, 16 Jun 2015 18:29:45 GMT

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?

Re: Load Script... help please

maxgro — Tue, 16 Jun 2015 18:52:39 GMT

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;

Re: Load Script... help please

Tue, 16 Jun 2015 19:08:26 GMT

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

Re: Load Script... help please

maxgro — Tue, 16 Jun 2015 19:20:52 GMT

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);

Re: Load Script... help please

Tue, 16 Jun 2015 19:40:50 GMT

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

Re: Load Script... help please

maxgro — Tue, 16 Jun 2015 19:52:00 GMT

there are no sales in 2014

please close this and open a new discussion