topic Re: Substring Mapping in QlikView

Substring Mapping

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Substring Mapping

sunny_talwar — Thu, 14 Sep 2017 12:55:42 GMT

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

];

Re: Substring Mapping

prieper — Thu, 14 Sep 2017 12:59:19 GMT

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

Re: Substring Mapping

Kushal_Chawda — Thu, 14 Sep 2017 13:41:27 GMT

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;

Re: Substring Mapping

Fri, 15 Sep 2017 11:55:38 GMT

Thanks for the prompt reply Sunny

Re: Substring Mapping

Fri, 15 Sep 2017 11:56:48 GMT

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

Re: Substring Mapping

Fri, 15 Sep 2017 11:57:33 GMT

Many thanks Kushal