Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | |
USAT | Missions |
USBR | Spanish |
USBRCM | Spanish Mission |
I need the result to be
Gift ID | Code | Group |
---|---|---|
001 | USAM1205 | |
002 | USAT0023 | Missions |
003 | USAM1109 | |
004 | AT0987 | Web |
005 | USBR1976 | Spanish |
006 | USBRCM | Spanish Mission |
007 | USAM--DD | |
008 | USAT--09 | Missions |
009 | USBR---- | Spanish |
010 | USAMJACKS | |
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
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:
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.
please include these fields in ur example (like ur first message); and I'll see what can be done
Ok, I updated the original post/tables to include the gifts with the new codes.
Thanks.
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);
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:
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?
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?
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 ...