Substring Mapping

[#1]

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

[sunny_talwar · Accepted solution]

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

];

[prieper]

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

[Kushal_Chawda]

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;

[#5]

Thanks for the prompt reply Sunny

[#6]

Thanks Peter...I have quite a large data set but will have a play with this to see how it performs.

[#7]

Many thanks Kushal