Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

misterkingsley
New Contributor III

Grouping Codes by Prefix with Group Table

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 IDCode
001USAM1205
002USAT0023
003USAM1109
004AT0987
005USBR1976
006USBRCM
007USAM--DD
008USAT--09
009USBR----
010USAMJACKS
011USBRG17H

CodeGroups

CodeGroup
ATWeb
USAMMail
USATMissions
USBRSpanish
USBRCMSpanish Mission

I need the result to be

Gift IDCodeGroup

001

USAM1205
Mail
002USAT0023Missions
003USAM1109Mail
004AT0987Web
005USBR1976Spanish
006USBRCMSpanish Mission
007USAM--DDMail
008USAT--09Missions
009USBR----Spanish
010USAMJACKSMail
011USBRG17HSpanish

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

Tags (2)
10 Replies
OmarBenSalem
Esteemed Contributor

Re: Grouping Codes by Prefix with Group Table

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:

Capture.PNG

misterkingsley
New Contributor III

Re: Grouping Codes by Prefix with Group Table

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
Esteemed Contributor

Re: Grouping Codes by Prefix with Group Table

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

misterkingsley
New Contributor III

Re: Grouping Codes by Prefix with Group Table

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

Thanks.

MVP
MVP

Re: Grouping Codes by Prefix with Group Table

1.png

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
Esteemed Contributor

Re: Grouping Codes by Prefix with Group Table

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:

Capture.PNG

misterkingsley
New Contributor III

Re: Grouping Codes by Prefix with Group Table

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?

misterkingsley
New Contributor III

Re: Grouping Codes by Prefix with Group Table

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
Contributor III

Re: Grouping Codes by Prefix with Group Table

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 ...

Untitled.png

Community Browser