topic Re: split sum ? in QlikView

split sum ?

selcukcadir — Mon, 06 May 2013 16:31:28 GMT

hi all,

Table 1

A1001

A1002

A1003

Table 2

row amount value

1 10 A1001 , A1002

2 20 A1002 , A1003

3 15 A1003

4 12 A1001

5 30 A1002

Result Chart Table :

Value Total Amount

A1001 22 info : (Row 1 + 4)

A1002 60 (Row 1 + 2 + 5)

A1003 35

How can I gather in this way? there is a comma between the values

Re: split sum ?

Gysbert_Wassenaar — Mon, 06 May 2013 17:12:41 GMT

When you load table 2 you can use the subfield function to create a record for every value in the comma separated list:

Table2:

Load row, amount, subfield(value, ',') as value

from ...table2source...;

Now you can use value as dimension and sum(amount) as expression in a table chart to get the totals you want.

Re: split sum ?

vivek_niti — Mon, 06 May 2013 17:17:08 GMT

Hi,

First split the data in the field Value to different columns:

tab1:

LOAD

row, amount,

Subfield(value,',',1) AS Value1,

Subfield(value,',',2) AS Value2

From.....

tab2:

LOAD row, amount, Value1 AS value

RESIDENT tab1;

CONCATENATE

LOAD row, amount, Value2 AS value

WHERE NOT ISNULL(Value2);

DROP TABLE tab1;

In front end just use the sum(amount) against value dimension..

Hope it helps....

Regards,

Vivek

Re: split sum ?

selcukcadir — Mon, 06 May 2013 18:26:25 GMT

Please Can you add a sample file ?

I could not.

thanks.

Re: split sum ?

selcukcadir — Mon, 06 May 2013 19:07:18 GMT

hi all,

Table 1

vaule pb

A1001 eur

A1002 usd

A1003 eur

Table 2

row amount value

1 10 A1001 , A1002

2 20 A1002 , A1003

3 15 A1003

4 12 A1001

5 30 A1002

Result Chart Table :

Value pb Total Amount

A1001 eur 22 info : (Row 1 + 4)

A1002 usd 60 (Row 1 + 2 + 5)

A1003 eur 35

How can I gather in this way? there is a comma between the values

I forgot to add pb

plese thaks.

Re: split sum ?

selcukcadir — Tue, 07 May 2013 05:18:07 GMT

Please help me ...