Grouping with wild cards - Qlik Community

bumin · ‎2013-02-22

Hi ,

I have the following problem:

accounts:

LOAD * INLINE [

account, value

A1000, 10000

A2000, 5000

B3000, -1000

B2200, -750

C7000, -800

];

bipotab:

LOAD * INLINE [

bipo, text

A, Sales

B, CoS

C, Admin

];

as result I want to have

Sales	A1000	10000
	A2000	5000
total		15000
CoS	B2200	-750
	B3000	-1000
total		-1750
Admin	C7000	-800
total		-800
total		12450

is there any way to do this. I cannot use IntervalMatch because A,B,C are not numeric.

thanks

Bumin

whiteline · ‎2013-02-22

Hi, just add another table:

linktable:

NoConcatenate

LOAD

account,

left(account,1) as bipo

Resident accounts;

bumin · ‎2013-02-22

thanks for the answer

but I had simplified the example

It is not allways the first digit

e.g. EINVER1010, EINVER1020, EINVER1030 have to be associated to EINVER

AUSZ_0510, AUSZ_0530, AUSZ_0540 habe to be associated to AUSZ

I am looking for a solution where I can work with wild cards

EINVER*

AUSZ*

thanks

Bumin

whiteline · ‎2013-02-22

linktable:

NoConcatenate

LOAD

account,

pick(wildmatch(account, 'EINVER*', 'AUSZ*'), 'EINVER', 'AUSZ') as bipo

Resident accounts;

You can easily add another prefixes.

rwunderlich · ‎2013-02-22

The Qlikview Cookbook http://robwunderlich.com/downloads

has an example named "Mapping with a table using wildcards" that shows how to use a mapping table with wildcards.

Qlikview Components http://qlikviewcomponents.org has an even easier method:

CALL Qvc.CreateWildMapExpression (vMapExpr, WildMapTable);

-Rob

http://robwunderlich.com