Scan the data and map their fields to another Table

[Anonymous]

Team,

I would like to scan the Comments field to get the the Number along with their data set. I have two table.

Source_Table1:

Number	Status
041349698	A
41350698	B
123487978	C
124750067	D
015599554	E
041348923	F
041349699	F

Source_Table 2:

ID	Code	Sample	Comments
41350554	N	CMOGHE	1) METER OK REPLACED MODULE2) 3) 4) 5) 6)NO#123487978#
41348929	Y	SEIAGH	1) CONST.1.34 P.V.102) HANG SET AND TIE IN3) AMI FLEX ID# 240066754) 5) 6)
41348923	Y	WUTS	1) CONSTANT=1.342) NO#015599554# / RDG-36172
41349552	N	CMOGHE	1) CONSTANT=1.032) No#041349552# / RDG-
41349698	N	SNMUT	1) SAME NO#041349699# RDG.

I want to scan the Comments column to get the Number and their details (Id,Code,sample). Mapped those details into Source_Table1 based upon the Number column which gets matched.

Example:

1. I'm scanning the comments of "ID=41350554" row to get the Number as "123487978"

2. And compared this number(123487978) to Source_Table1

3. If match row in Source_Table1 , i need to map the (Id=41350554,Code=N,Sample=CMOGHE) to the Number 123487978(In Source_Table1 )

My Final Output will be,

Number	Status	Code	Sample
123487978	C	N	CMOGHE
15599554	E	Y	WUTS
41349699	F	N	SNMUT

Can any one suggest me the output for this.

Thanks,

Shakila

[MarcoWedel]

Hi,

in case there might be more than one Number in the Comments, you might try with something like this:

Source_Table1:
LOAD Number, 
    Status
FROM [https://community.qlik.com/thread/278937] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 9))));

Source_Table2:
LOAD ID, 
    Code, 
    Sample, 
    Comments
FROM [https://community.qlik.com/thread/278937] (html, codepage is 1252, embedded labels, table is @2);

mapNumber:
Mapping
LOAD Number, '@start@'&Number&'@end@'
Resident Source_Table1;

tabLink:
LOAD ID,
    TextBetween(CommNum,'@start@','@end@',IterNo()) as Number
While IterNo()<=SubStringCount(CommNum,'@start@');
LOAD ID,
    MapSubString('mapNumber',Comments) as CommNum
Resident Source_Table2;

hope this helps

regards

Marco

[qliksus]

1) I appended the status field with a string in this case its "Found" so the data will be something like Found&'A'&Found ,  Found&B&Found ,

2)  Mapsubstring will look for the matching substring your are joining with and if a match is found the Status column is returned

3)  The field returned now has the value as  Found&'A'&Found ,  Found&B&Found ............... so just use the

textbetween function to look for your result

Hope this clarifies