topic Re: Group items in a dimension table in QlikView

Group items in a dimension table

Thu, 03 Apr 2014 09:57:19 GMT

I have a dimension table in my script and I want to group the data items so if i have:

Expense AE

Expense AA

Expense AC

These should appear in Expense A

Re: Group items in a dimension table

its_anandrjs — Thu, 03 Apr 2014 10:01:22 GMT

this are the fields or the table names

Re: Group items in a dimension table

Thu, 03 Apr 2014 10:02:53 GMT

these data lines in a table.

Re: Group items in a dimension table

its_anandrjs — Thu, 03 Apr 2014 10:13:12 GMT

Write like for new field type

if( Match(Yourfieldname,'Expense AE','Expense AA','Expense AC'),'Expense A','Other') as NewFieldtype

Re: Group items in a dimension table

Thu, 03 Apr 2014 10:14:45 GMT

is this in the script?

Re: Group items in a dimension table

its_anandrjs — Thu, 03 Apr 2014 10:19:22 GMT

Yes in the load script like

if( Match(Yourfieldname,'Expense AE','Expense AA','Expense AC'),'Expense A','Other') as NewFieldtype

and in dimension or expression

=if( Match(Yourfieldname,'Expense AE','Expense AA','Expense AC'),'Expense A','Other')

Re: Group items in a dimension table

Thu, 03 Apr 2014 10:46:41 GMT

ok thanks.

What about if i have many to of this so as above but also Expense AB, Expense AF AS Expense C

Re: Group items in a dimension table

its_anandrjs — Thu, 03 Apr 2014 10:47:10 GMT

If possible provide some sample data for this.

Re: Group items in a dimension table

Thu, 03 Apr 2014 10:49:49 GMT

LocalPointOfDelivery	LocalPointOfDeliveryDescription	Category
A&E	A&E	AE
All	All	AL
ATFAO	Attended Outpatients - first attendance	ATFAO
ATFUO	Attended Outpatients - follow up	ATFUO
ATOP	Outpatient Attendances excl DNA	ATOP
CC	Critical Care	AL
COM	Community	AL
DA	Direct Access	DIR
DC	Daycase	DC
ELIP	Elective Inpatient	EIP
IP	All in-patients	IP
NEL	Non Elective Inpatient	NEIP
OP	Outpatient Attendance	OP
OPFA	Outpatients FA	ATFAO
OPFU	Outpatients FU	ATFUO
OTH	Other	AL
WA	Ward attenders	OP

So in the above

I want AFAO, AFTUO to be under Out, and the all others as they are. This will then want to be be in a list object as a filter.

Re: Group items in a dimension table

its_anandrjs — Thu, 03 Apr 2014 10:52:39 GMT

In which column that is Category

Re: Group items in a dimension table

Thu, 03 Apr 2014 10:56:18 GMT

yeah or LocalPointofdelivery

Re: Group items in a dimension table

its_anandrjs — Thu, 03 Apr 2014 11:01:07 GMT

the values AFAO, AFTUO are not available in the fields.

Re: Group items in a dimension table

Thu, 03 Apr 2014 11:03:09 GMT

typo ATFAO and ATFUO

Re: Group items in a dimension table

its_anandrjs — Thu, 03 Apr 2014 11:17:10 GMT

Ok in your load statement put a code like this

If(Match(Category,'ATFAO','ATFUO'),'Out',Category) as NewCat

By this your 'ATFAO','ATFUO' are goes to Out category as what ever you want and other is same category. In the same above you can add for others category also what ever you decide i done here for category field.

Hope this helps

Re: Group items in a dimension table

Thu, 03 Apr 2014 12:33:42 GMT

thats worked. Thank you for your help

Re: Group items in a dimension table

its_anandrjs — Thu, 03 Apr 2014 12:37:30 GMT

You are welcome....

Thanks & Regards