Parsing Text for specific keywords

[manojkulkarni]

Hi All,

I want to get specific texts from the given field in QlikView application. Please advice me how we achieve the result. Sample and out is as below:

Data:

1. Secondary-MTNL-256 Kbps-24548154

2. Primary-Airtel- 1Gbps-769411542112

3. @@@ 1Gbps TCL P2P Primary Link (2_10.63.0.12 )

Output required

1. MTNL & 256 Kbps

2. Airtel & 1Gbps

3. TCL & 1Gbps

[sunny_talwar]

Is there a pattern to this text which can be programmed to extract? Like for Gbps part... it seems that we would want to pick anything that ends with Gbps and the number before it.... could there be Mbps too? What about the first part any particular list of names that we should look for in the string?

[manojkulkarni]

Pattern is somewhat like number followed by KBPS/MBPS/GBPS etc but it is not fixed with position in text.

[sunny_talwar]

That part is easy to code... but what about the pattern for MTNL, Aitel, or TCL?

[manojkulkarni]

These are fixed keywords to be identified in the given text.

[sunny_talwar]

Right, but is there a list of fixed keywords to look for?

[sunny_talwar]

May be this

MappingTable1:
Mapping
LOAD F1,
	 '<' & F1 & '>' as F2
INLINE [
    F1
    MTNL
    Airtel
    TCL
];

MappingTable2:
Mapping
LOAD F1,
	 '<' & F1 & '>' as F2
INLINE [
    F1
    Mbps
    Kbps
    Gbps
];

MappingTable3:
Mapping
LOAD Pow(2, IterNo()-1),
	 '<' & Pow(2, IterNo()-1) & '>'
AutoGenerate 1
While IterNo() <= 10;

Table:
LOAD Data,
	 TextBetween(MapSubString('MappingTable1', Data), '<', '>') & ' & ' & TextBetween(MapSubString('MappingTable3', Data), '<', '>') & TextBetween(MapSubString('MappingTable2', Data), '<', '>') as ParsedData,
	 TextBetween(MapSubString('MappingTable1', Data), '<', '>') as Temp1,
	 TextBetween(MapSubString('MappingTable2', Data), '<', '>') as Temp2,
	 TextBetween(MapSubString('MappingTable3', Data), '<', '>') as Temp3;
LOAD * INLINE [
    Data
    Secondary-MTNL-256 Kbps-24548154
    Secondary-MTNL-512 Kbps-24548154
    Primary-Airtel- 1Gbps-769411542112
    @@@ 1Gbps TCL P2P Primary Link (2_10.63.0.12 )
];

[Brett_Bleess]

Manoj, did Sunny's post and sample application get you what you needed?  If so, do not forget to return to your post and use the Accept as Solution button on that post of Sunny's to give him credit for the help and let other Community Members know it worked for you.  If you are still working on things, leave an update on what you still need.

Regards,
Brett