Using Text as Field in Sum function

[chhavi376]

Hi All,

I cannot share the QV file but i am explaining a scenario where i am stuck.

There is one field in the data:

TYPE

c1

c2

c3

c4

ANd there is another table in my data like:

c1    c2    c3    c4

1      1        0        1

1      0        1        1

I want a table(on UI) like:

Type          sum()

c1            2

c2            1

c3            1

c4            2

So i am trying to use SUM(fieldvalue('Type',RowNo)) function to get the sum. The problem is that QV is not able to identify that the return value of fieldvalue() function is a column name.

[kaushiknsolanki]

Hi,

Instead of using any function why dont you crosstab the table which you have using Crosstable() function?

Regards,

Kaushik Solanki

[chhavi376]

I need to take the values from the base table only .. as i have to maintain the associativity.. Taking values from cross table will not have that relationship

[MK_QSL]

This can easily be achieved using crosstable functionality.

T1:
Load * Inline
[
 TYPE
 C1
 C2
 C3
 C4
];

T2:
CrossTable(TYPE,Value)
Load RowNo(),* Inline
[
 C1, C2, C3, C4
 1, 1, 0, 1
 1, 0, 1, 1
];

Now use Straight Table/Pivot Table

Dimension TYPE

Expression

SUM(Value)

[MK_QSL]

Or something like this

T1:

Load * Inline

[

TYPE

C1

C2

C3

C4

];

T2:

CrossTable(TYPE,Value)

Load RowNo(),* Inline

[

C1, C2, C3, C4

1, 1, 0, 1

1, 0, 1, 1

];

Left Join (T1)

Load TYPE,SUM(Value) as TotalValue Resident T2 Group By TYPE;

Drop Table T2;

T2:

Load * Inline

[

C1, C2, C3, C4

1, 1, 0, 1

1, 0, 1, 1

];

[chhavi376]

Hi Manish,

I tried this already ...

But i need to to maintain the associativity between these columns(c1,c2,c3,c4). as user will have an option to filter on these columns also. and he wants to view the sum according to the filters selected

[MK_QSL]

check my 2nd reply

[chhavi376]

In this case the sum will not vary with the selections in c1/2/3/4

[chhavi376]

Is there any way in which i can convert the returned value of fieldvalue() to the column name?

[tresesco · Accepted solution]

For a total UI solution try like:

Pick(Match(TYPE,$(=Concat(DISTINCT chr(39)&TYPE&chr(39), ','))),

  $(=Concat(DISTINCT 'Sum([' &TYPE&'])' ,','))

  )