Grouping Codes by Prefix with Group Table

[Anonymous]

Hello All,

I have a list of gifts to our organization that come in with a Solicitation Code (CODE). I also have a requirement to group gifts by solicitation code using a CodeGroups Table. The issue I'm having is that the Mapping Table only uses the prefixes of the codes.

Gifts

Gift ID	Code
001	USAM1205
002	USAT0023
003	USAM1109
004	AT0987
005	USBR1976
006	USBRCM
007	USAM--DD
008	USAT--09
009	USBR----
010	USAMJACKS
011	USBRG17H

CodeGroups

Code	Group
AT	Web
USAM	Mail
USAT	Missions
USBR	Spanish
USBRCM	Spanish Mission

I need the result to be

Gift ID	Code	Group
001	USAM1205	Mail
002	USAT0023	Missions
003	USAM1109	Mail
004	AT0987	Web
005	USBR1976	Spanish
006	USBRCM	Spanish Mission
007	USAM--DD	Mail
008	USAT--09	Missions
009	USBR----	Spanish
010	USAMJACKS	Mail
011	USBRG17H	Spanish

Any one know of a solution for something like this where the prefix length can vary?

EDITED** Added gifts 007, 008, and 009

EDIT@* Added Gifts 010 and 011

[OmarBenSalem]

Gifts:

load * , PurgeChar(Code,'0123456789') as CodeID  Inline [

GiftID, Code

001, USAM1205

002, USAT0023

003, USAM1109

004, AT0987

005, USBR1976

006, USBRCM

];

left Join(Gifts)

load Group, Code as CodeID Inline [

Code, Group

AT, Web

USAM, Mail

USAT, Missions

USBR, Spanish

USBRCM, Spanish Mission

];

drop Field CodeID;

result:

[Anonymous]

Omar,

Thanks for the reply. I should've included more examples. My fault. There are other Codes like this as well:

USAM--DD

USAT--09.

Any idea how to handle those additionally?

I'm looking for other odd ones as well to make sure all of the use cases are taken care of.

Thanks for the help.

[OmarBenSalem]

please include these fields in ur example (like ur first message); and I'll see what can be done

[Anonymous]

Ok, I updated the original post/tables to include the gifts with the new codes.

Thanks.

[maxgro]

tmp:

LOAD Code,

     Group,

     Len(Code) as LenCode

FROM

[https://community.qlik.com/thread/284232]

(html, codepage is 1252, embedded labels, table is @2);

mapCodeGroups:

MAPPING

LOAD Code,

     Group & '@@@'

Resident tmp

ORDER BY LenCode desc;

DROP Table tmp;

Gifts:

LOAD [Gift ID],

     Code,

SubField(MapSubString('mapCodeGroups', Code), '@@@', 1) as Group

FROM

[https://community.qlik.com/thread/284232]

(html, codepage is 1252, embedded labels, table is @1);

[OmarBenSalem]

Gifts:

load * , SubField(PurgeChar(Code,'0123456789'),'-',1) as CodeID  Inline [

GiftID, Code

001, USAM1205

002, USAT0023

003, USAM1109

004, AT0987

005, USBR1976

006, USBRCM

007, USAM--DD

008, USAT--09

009, USBR----

];

left Join(Gifts)

load Group, Code as CodeID Inline [

Code, Group

AT, Web

USAM, Mail

USAT, Missions

USBR, Spanish

USBRCM, Spanish Mission

];

drop Field CodeID;

result:

[Anonymous]

Thank you again Omar. So far this works for everything but one case (I'm still looking for others). I may not have explained it well in the beginning.

I have another Code like "USAMJACKS" or "USBRG17H".

I'll update the original post with these new Codes.

Can these be solved as well?

[Anonymous]

This seems to be working well. How do I add a portion that will return a value of "ERROR:MISSING CODE" if the Code is not in the mapping table?

[kaanerisen]

Hi,

You can try this

Gifts:

LOAD [Gift ID],

     Code,

If(MapSubString('mapCodeGroups', Code)<>Code,SubField(MapSubString('mapCodeGroups', Code), '@@@', 1),'ERROR:MISSING CODE') as Group

FROM ...