topic Re: Mapping load and sum() in QlikView

Mapping load and sum()

segerchr — Wed, 16 Mar 2016 14:20:43 GMT

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

Re: Mapping load and sum()

sunny_talwar — Wed, 16 Mar 2016 20:15:35 GMT

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

];

Re: Mapping load and sum()

segerchr — Thu, 17 Mar 2016 14:15:45 GMT

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

Re: Mapping load and sum()

sunny_talwar — Thu, 17 Mar 2016 14:28:30 GMT

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

Re: Mapping load and sum()

segerchr — Thu, 17 Mar 2016 15:19:09 GMT

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

Re: Mapping load and sum()

sunny_talwar — Thu, 17 Mar 2016 15:48:01 GMT

Try this:

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