Mapping load and sum()

[segerchr]

Hi,

i am searching for a solution which can map "Gewerk" and "Price" to the Table 2.

I am using mapping load, but the Price did not sum. Can anybody help `?

IS:

Table1:

Gewerk,	Price,	Work
Building,	150	Roof
Planning,	100	Kitchen
Building,	50	TV

Table2:

Gewerk,	PriceREAL,	PRICEFUTURE
Buliding	350	400
Planning	250	150

Taget:

Gewerk,	PriceREAL,	PRICEFUTURE	Price_new
Buliding,	350,	400,	200
Planning,	250,	150	100

Thanks

C. Seger

[sunny_talwar · Accepted solution]

May be this:

MappingTable:

Mapping

LOAD Gewerk,

  Sum(Price) as Price

Group By Gewerk;

LOAD * Inline [

Gewerk, Price, Work 

Building,  150, Roof

Planning,  100, Kitchen

Building,    50, TV

];

FinalTable:

LOAD *,

  ApplyMap('MappingTable', Gewerk) as Price_new;

LOAD * Inline [

Gewerk, PriceREAL,  PRICEFUTURE

Building, 350,  400

Planning,  250,  150

];

[segerchr]

Here is my last question, i hope so 🙂

I will use a text box to display a extrapolation.

Here is my code, but how can i check each line with the code and finally sum the result.

The result should be: 500

[sunny_talwar]

Would you be able to tell me outside of QlikView how you are coming up with 500?

[segerchr]

Hey,

=if (PriceREAL = 350, PRICEFUTURE,if(finished='x',Price_new,PriceREAL))

for line 1(building): PriceReal is 350, so wie have to take 400

for line 2(Planning): PriceReal is not 350, so we take the next if. If Fisihed =x, than Price_New, so we have to take 100. After all we should add every line result.

For1: 400

For2: 100

Result: 500

[sunny_talwar]

Try this:

=Sum(Aggr(If(PriceREAL = 350, PRICEFUTURE,If(finished='x',Price_new,PriceREAL)), Gewerk))