topic Re: Scan the data in Comments. in QlikView

Scan the data and map their fields to another Table

Anonymous — Mon, 23 Oct 2017 18:50:33 GMT

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

Re: Scan the data in Comments.

vishsaggi — Mon, 23 Oct 2017 18:57:53 GMT

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

Re: Scan the data in Comments.

Anonymous — Mon, 23 Oct 2017 19:24:08 GMT

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

Re: Scan the data and map their fields to another Table

maxgro — Mon, 23 Oct 2017 19:46:47 GMT

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

];

Re: Scan the data in Comments.

vishsaggi — Mon, 23 Oct 2017 19:52:48 GMT

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.

Re: Scan the data in Comments.

Anonymous — Mon, 23 Oct 2017 20:28:04 GMT

Yes, it is working. Thank you guys.

Re: Scan the data in Comments.

Anonymous — Tue, 24 Oct 2017 09:40:24 GMT

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)

Re: Scan the data and map their fields to another Table

vishsaggi — Tue, 24 Oct 2017 14:06:35 GMT

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.

Re: Scan the data and map their fields to another Table

qliksus — Wed, 25 Oct 2017 09:31:32 GMT

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.
];

Re: Scan the data and map their fields to another Table

Anonymous — Wed, 25 Oct 2017 17:23:09 GMT

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

Re: Scan the data and map their fields to another Table

MarcoWedel — Wed, 25 Oct 2017 21:28:06 GMT

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

Re: Scan the data and map their fields to another Table

qliksus — Thu, 26 Oct 2017 02:45:21 GMT

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