topic Re: Using Text as Field in Sum function in QlikView

Using Text as Field in Sum function

chhavi376 — Wed, 31 Jan 2018 09:54:26 GMT

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

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.

Re: Using Text as Field in Sum function

kaushiknsolanki — Wed, 31 Jan 2018 09:58:09 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Using Text as Field in Sum function

chhavi376 — Wed, 31 Jan 2018 10:01:06 GMT

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

Re: Using Text as Field in Sum function

MK_QSL — Wed, 31 Jan 2018 10:02:19 GMT

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)

Re: Using Text as Field in Sum function

MK_QSL — Wed, 31 Jan 2018 10:04:33 GMT

Or something like this

T1:

Load * Inline

[

TYPE

];

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

];

Re: Using Text as Field in Sum function

chhavi376 — Wed, 31 Jan 2018 10:12:46 GMT

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

Re: Using Text as Field in Sum function

MK_QSL — Wed, 31 Jan 2018 10:16:17 GMT

check my 2nd reply

Re: Using Text as Field in Sum function

chhavi376 — Wed, 31 Jan 2018 10:34:58 GMT

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

Re: Using Text as Field in Sum function

chhavi376 — Wed, 31 Jan 2018 10:40:18 GMT

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

Re: Using Text as Field in Sum function

tresB — Wed, 31 Jan 2018 11:04:41 GMT

For a total UI solution try like:

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

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

)

Re: Using Text as Field in Sum function

chhavi376 — Wed, 31 Jan 2018 11:33:46 GMT

You are a savior!

Re: Using Text as Field in Sum function

chhavi376 — Thu, 01 Feb 2018 06:19:48 GMT

Hi Tresesco,

Your expression worked perfectly for me.

But there is another addition which is required now.

they want the sum where type=1 for that particular type..

if we are calculating sum for say, c1, instead of sum(c1) it should be sum({<c1={1}>}c1).. for c2, sum({<c2={1}>}c2). same for c3, c4 and so on

I avoided creating another discussion because it would lead to confusion.

Can you please help me in the same?

Regards,

Chhavi

Re: Using Text as Field in Sum function

chhavi376 — Thu, 01 Feb 2018 09:18:29 GMT

Tresesco.. can you help?

Re: Using Text as Field in Sum function

tresB — Thu, 01 Feb 2018 09:44:51 GMT

Is this specific to C1 only or applicable to all cases?

Re: Using Text as Field in Sum function

chhavi376 — Thu, 01 Feb 2018 09:48:22 GMT

All cases.

Something like this:

c1 sum({<c1={1}>}c1)..

c2 sum({<c2={1}>}c2)..

c3 sum({<c3={1}>}c3)..

c4 sum({<c4={1}>}c4)..

Re: Using Text as Field in Sum function

tresB — Thu, 01 Feb 2018 09:59:14 GMT

Try this:

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

$(=Concat(DISTINCT 'Sum({<'

&TYPE&

'={1}>}['

&TYPE&'])' ,','))

)

Re: Using Text as Field in Sum function

chhavi376 — Thu, 01 Feb 2018 10:08:39 GMT

I have already made a variable which is to be used as set analysis

If(GetSelectedCount([Type Indication])>=1,

'{<'&Type&'={1}' &Concat(DISTINCT SubField([Type Indication],':',1)& if( SubField([Type Indication],':',2)=' Require', '={1}','={0}'))&'>}'

)

Adding Type&'={1}' is giving internal error

Re: Using Text as Field in Sum function

chhavi376 — Thu, 01 Feb 2018 10:14:31 GMT

This variable has been used in the expression u suggested:

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

$(=Concat(DISTINCT 'Sum($(test)['

&Type&'])' ,','))

)

Re: Using Text as Field in Sum function

tresB — Thu, 01 Feb 2018 10:18:06 GMT

Hi Chhavi,

If you want me to help with your actual qv, try to share the same with sample data. That would help me help you.

Re: Using Text as Field in Sum function

chhavi376 — Thu, 01 Feb 2018 10:24:24 GMT

Give me 15 min.. I will update it..