Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

segerchr
New Contributor II

Mapping load and sum()

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,   150Roof
Planning,   100Kitchen
Building,     50TV

Table2:

Gewerk, PriceREAL,   PRICEFUTURE
Buliding 350   400
Planning   250   150

Taget:

Gewerk, PriceREAL,   PRICEFUTUREPrice_new
Buliding,  350,   400,200
Planning,   250,   150100

Thanks

C. Seger

1 Solution

Accepted Solutions

Re: Mapping load and sum()

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

];


Capture.PNG

5 Replies

Re: Mapping load and sum()

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

];


Capture.PNG

segerchr
New Contributor II

Re: Mapping load and sum()

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

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

segerchr
New Contributor II

Re: Mapping load and sum()

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

Try this:

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

Community Browser