topic Value (Expression) Field Groups in QlikView

Value (Expression) Field Groups

Fri, 05 Aug 2011 00:51:40 GMT

Hi,

I have a data table (cross table) with a large number of rows and multiple value fields per record/row. e.g.

Company,Account,Owner, Balance, Validated Balance, Debit Unvalidated Balance, CR Unvalidated Balance, Net Unvalidated Balance, P&L DR, P&L CR, P&L

1234, 123456789, PersonA, 1000, 500, 800,-300, 500, 15, -10, 5

I am trying to present this in a pivot table with:

Multiple Expressions for each value based on simple sums and counts/% - no problem with this
Headings to group the expressions into logical groups e.g. Balances (all metrics based on Balance), Unvalidated (all metrics using the unvalidated value fields), PL

		Balance		Unvalidated Balances			P&L
Company	Owner	Balance	% of Total Balance	Debit Unvalidated Balance	CR Unvalidated Balance	Net Unvalidated Balance	P&L DR	P&L CR	P&L
1234	PersonA	1000	17%	800	-300	500	15	-10	5
1234	PersonB	2000	33%	0	0	0	0	0	0
1234	PersonC	3000	50%	3000	0	3000	15	-50	-35

Question: How can I create the heading groups (i.e. Balance, Unvalidated Balances, P&L above)?

I considered turning the cross table into a straight table and applying a mapping to the value type to make this a stored value that could be used as dimension. However, in addition to wanting to avoid manual mappings using data not able to be extracted with the source data, the data volumes made it impossible as I ran out of RAM.

Any ideas?

Value (Expression) Field Groups

rahulgupta — Fri, 05 Aug 2011 05:09:41 GMT

Hey Hi,

You can use text objects instead...

make an inline of :

Load * Inline [

Heading, SubHeading

Balances, Balance

Balances, [% Of Total Balances]

......

];

Regards

Value (Expression) Field Groups

Fri, 05 Aug 2011 06:20:22 GMT

Thanks Rahul,

My preference is not to use inline. Could you perhaps expand on what you mean by text objects?

Many thanks

Value (Expression) Field Groups

Mon, 08 Aug 2011 14:42:33 GMT

I have experiance doing this in Crystal mainly, but I have done it in Qlikview.

You values need to have a field that you can reference that will put it in each group. You would then create a formula that displays a text field if it falls in that group.

Here is an example I created to sort Order amounts by value:

If(orderamt<25000,'1. Under 25K',If((orderamt>=25000 and orderamt<50000),'2. 25K–50K',If((orderamt>=50000 and orderamt<100000),'3. 50K to 100K',If((orderamt>=100000 and orderamt<200000),'4. 100K to 200K',If((orderamt>=200000 and orderamt<300000),'5. 200K to 300K',If((orderamt>=300000 and orderamt<400000),'6. 300K to 400K',If((orderamt>=400000 and orderamt<500000),'7. 400K to 500K',if(orderamt>=500000,'8. Over 500K'))))))))as OrderLvl,

Value (Expression) Field Groups

its_anandrjs — Mon, 08 Aug 2011 15:19:38 GMT

Hi jonwebster,

Try to use the variables to create a expresion and then use it on the straight table as a expression.

Anand