Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I need to perform mapping based on substring search within a field. I have a "Notes" text field that contains contract numbers and I want to link all contract records to that Note where the contract number appears in the text. I;ve laid out an example scenario & desired outcome below.
Thanks in advance for any help on this
Source Tables -
NoteID | Notes |
---|---|
1 | Relating to contracts num 123456 and 987654 |
2 | Unmatched cash on contracts 852741, 963852... |
ContractID | ContractNum |
---|---|
1 | 123456 |
2 | 987654 |
3 | 852741 |
4 | 963852 |
The desired result would be either:
A) a mapping table
NoteID | ContractID |
---|---|
1 | 1 |
1 | 2 |
2 | 3 |
2 | 4 |
B) a join to the Notes table
NoteID | Notes | ContractID |
---|---|---|
1 | Relating to contracts num 123456 and 987654 | 1 |
1 | Relating to contracts num 123456 and 987654 | 2 |
2 | Unmatched cash on contracts 852741, 963852... | 3 |
2 | Unmatched cash on contracts 852741, 963852... | 4 |
Best Regards
Gareth
May be this
MappingTable:
Mapping
LOAD ContractNum,
'/' & ContractID & '\';
LOAD * INLINE [
ContractID, ContractNum
1, 123456
2, 987654
3, 852741
4, 963852
];
FactTable:
LOAD NoteID,
Notes,
SubField(ContractID, '\', 1) as ContractID
Where IsNum(SubField(ContractID, '\', 1));
LOAD NoteID,
Notes,
SubField(ContractID, '/') as ContractID;
LOAD *,
MapSubString('MappingTable', Notes) as ContractID;
LOAD * INLINE [
NoteID, Notes
1, Relating to contracts num 123456 and 987654
2, "Unmatched cash on contracts 852741, 963852..."
];
May be this
MappingTable:
Mapping
LOAD ContractNum,
'/' & ContractID & '\';
LOAD * INLINE [
ContractID, ContractNum
1, 123456
2, 987654
3, 852741
4, 963852
];
FactTable:
LOAD NoteID,
Notes,
SubField(ContractID, '\', 1) as ContractID
Where IsNum(SubField(ContractID, '\', 1));
LOAD NoteID,
Notes,
SubField(ContractID, '/') as ContractID;
LOAD *,
MapSubString('MappingTable', Notes) as ContractID;
LOAD * INLINE [
NoteID, Notes
1, Relating to contracts num 123456 and 987654
2, "Unmatched cash on contracts 852741, 963852..."
];
A good starter would be to load the contracts first and then to normalize the Notes-Table:
Contracts: LOAD ContractID, ContractNum FROM ...;
Notes: LOAD NoteID, Notes FROM ....;
FOR i = 0 TO NOOFROWS('Contracts')
LET vContractID = PEEK('ContractID', i, 'Contracts');
LET vContractNum = PEEK('ContractNum', i, 'Contracts');
NotesNormalized: LOAD NoteID, '$(vContractID)' AS ContractID RESIDENT Notes WHERE WILDMATCH(Notes, '*$(vContractNum)*');
NEXT i
another way
MappingTable:
Mapping
LOAD ContractNum,'@' & ContractNum & '@' as MapContractNum INLINE [
ContractID, ContractNum
1, 123456
2, 987654
3, 852741
4, 963852
];
Final:
LOAD *,
SubField(Mapped,'@') as ContractNum;
LOAD *, MapSubString('MappingTable',Notes) as Mapped INLINE [
NoteID, Notes
1, Relating to contracts num 123456 and 987654
2, "Unmatched cash on contracts 852741, 963852..."
];
Inner Join(Final)
LOAD * INLINE [
ContractID, ContractNum
1, 123456
2, 987654
3, 852741
4, 963852
];
DROP Field Mapped;
Thanks for the prompt reply Sunny
Thanks Peter...I have quite a large data set but will have a play with this to see how it performs.
Many thanks Kushal