Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

bumin
Contributor II

Grouping with wild cards

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

SalesA100010000

A20005000
total
15000
CoSB2200-750

B3000-1000
total
-1750
AdminC7000-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

4 Replies
whiteline
Honored Contributor II

Re: Grouping with wild cards

Hi, just add another table:

linktable:

NoConcatenate

LOAD

     account,

     left(account,1) as bipo

Resident accounts;

bumin
Contributor II

Re: Grouping with wild cards

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
Honored Contributor II

Re: Grouping with wild cards

linktable:

NoConcatenate

LOAD

      account,

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

Resident accounts;

You can easily add another prefixes.

Re: Grouping with wild cards

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

Community Browser