Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Question not clear can you give use more details? An example with your expected output would help.
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 |
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
];
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.
Yes, it is working. Thank you guys.
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:
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
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)
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.
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.
];
Can you please tell how the found status concept will work?