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