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

[vishsaggi]

Question not clear can you give use more details? An example with your expected output would help.

[Anonymous]

I provided the Expected output below. The below 3 numbers are Matched number by comparing with Source_Table1 (Columnà Number) and Scanned Numbers from Comments column of Source_Table2.

Have taken the matched Number data set (Number,ID,Code,Sample) into final table. 

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

[maxgro]

Source_Table1:

Mapping

load * inline [

Number, Status

041349698, A

41350698, B

123487978, C

124750067, D

015599554, E

041348923, F

041349699, F

];

Source_Table2:

load

*

Where Status <> 'MISSING';

load

*,

subfield(Comments, '#', 2) as Number,

ApplyMap('Source_Table1', subfield(Comments, '#', 2), 'MISSING') as Status

inline [

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

];

[vishsaggi · Accepted solution]

Try this?

Source_Table1:

LOAD * INLINE [

Number, Status

041349698, A

41350698, B

123487978, C

124750067, D

015599554, E

041348923, F

041349699, F

];

Source_Table2:

LOAD *, TextBetween(Comments, '#', '#') AS Number INLINE [

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.

];

Using Straight table add the relevant field names and suppress when value is null for all the dimensions added.

Add and empty expression with a value 1 and hide that column.

[Anonymous]

Yes, it is working. Thank you guys.

[Anonymous]

Hi team,

Thanks for the help.

I need one more concerns also, my data set is unstructured manner i.e.  if the Comments column have the number without any special character how could we get the results. Provided the Source

Source_Table2:

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 Number 041349699 RDG.

I tried this with by using Wildcard search () and Index (), but hitting the Server performance. 

Can anyone share the alternative solution for this?

Here is my code:

Wild Card search:

Source_Table1:

LOAD * INLINE [

Number, Status

041349698, A

41350698, B

123487978, C

124750067, D

015599554, E

041348923, F

041349699, F

];

LET rowMeters = NoOfRows(' Source_Table1’);  
for i=1 to $( rowMeters)                      // loop through every row
          let vID = FieldValue(Number,$(i));
     
if( len('$( vID)')           >0) THEN  

Source_Table2:

LOAD ID, Code, Sample, Comments
FROM
..\data\Source2.xlsx
(ooxml, embedded labels, table is Sheet1)

where wildmatch( Comments,'*'&$( vID)&'*')
;

Actual:
LOAD *, '$( vID)' as Final_Number Resident FieldOrders;

DROP Table FieldOrders;
ENDIF

NEXT

Index ();

Source_Table1:

LOAD ID, Code, Sample, Comments
FROM
..\data\Source2.xlsx
(ooxml, embedded labels, table is Sheet1)

where wildmatch( Comments,'*'&$( vID)&'*')
;

Outer join (Source_Table1)         

LOAD * INLINE [

Number, Status

041349698, A

41350698, B

123487978, C

124750067, D

015599554, E

041348923, F

041349699, F

];

Final_Data:

Load * resident Source_Table1

Where index (Comments, Number)

[vishsaggi]

May be not a straight forward function, you have to use KeepChar() and Subfield() functions here.

If you can give us the whole comments field with values we can look into it. Else try using

KeepChar(Comments, '0123456789)#/') then use subfield()....Something like that.

[qliksus]

If its a unstructured data create a format which wont be present in your comments and fetch the status . Append the words Found between the status and use mapsubstring to get the result

map1:
mapping LOAD Number,'Found'&Status&'Found' as Status INLINE [
   
    Number, Status
    41349698, A
    41350698, B
    123487978, C
    124750067, D
    15599554, E
    41348923, F
    41349699, F
];

LOAD *, textbetween(MapSubString('map1',comments),'Found','Found') as Status INLINE [   
    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.
];

[Anonymous]

Can you please tell how the found status concept will work?