Parsing Data from Complex Fields
- Sarabjeet Kaur
Analytics
Intellicus supports parsing complex data stored in data field.
The complex data could be passed as:
XML
CSV
Regular expression
Fixed length
You can specify various functions to extract values from complex data fields under the Formula tab.
Figure 3: Formula Tab
Certain functions that are supported are given below:
Fields with XML data
Method | <COLUMN_NAME>.getXMLValue(String xPathQuery) |
Return | String |
Usage | FormulaField1 = DATA_XML.getXMLValue(“PARAMETERNAME[1]”); |
Input
| <PARAMETER>
<NAME>param1</NAME> <NAME>param2</NAME> <PARAMETER> |
Output | param1 |
Method | <COLUMN_NAME>.getXMLValue(String xPathQuery, String defaultValue) |
Return | String |
Usage | FormulaField1 = DATA_XML.getXMLValue(“PARAMETERVALUE”, “NONE”); |
Input | <PARAMETER>
<NAME>param1</NAME> <NAME>param2</NAME> <PARAMETER> |
Output | NONE |
Fields with CSV data
Method | <COLUMN_NAME>.getCSVValue(int columnIndex) |
Return | String |
Usage | FormulaField1 = DATA_CSV. getCSVValue(2); |
Input | John,29,USA,302765 |
Output | 29 |
Method | <COLUMN_NAME>.getCSVValue(int columnIndex, String separator, String enclosedBy, String defaultValue) |
Return | String |
Usage | FormulaField1 = DATA_CSV. getCSVValue(2,”|”,”””,”NONE”); |
Input | John|29|USA|302765 |
Output | 29 |
Method | <COLUMN_NAME>.getCSVValues() |
Return | String array |
Usage | FormulaField1 = DATA_CSV. getCSVValues(); |
Input | John,29,USA,302765 |
Output | [John,29,USA,302765] |
Method | <COLUMN_NAME>.getCSVValues(String separator, String enclosedBy) |
Return | String array |
Usage | FormulaField1 = DATA_CSV. getCSVValues(“|”,”””); |
Input | John|29|USA|302765 |
Output | [John,29,USA,302765] |
Fields with Regular Expression data
Method | <COLUMN_NAME>.getREGValue(String regexPattern) |
Return | String |
Usage | FormulaField1 = DATA_REG. getREGValue (“(b(?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)((?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)(d+)(^A)([[^]]*])(^A)(200|500)(^A)(d+)(^A)([a-zA-Z0-9-_]+)(^A?)(1|0|-)(^A?)(-|d+)(^(d+)?)(^(d+)?)(^(d+(.d+))?)(^(d+(.d+))?)(^(d+(.d+))?)(^ASID^B(d+))”); |
Input | 10.70.110.94^A10.71.173.21^A9077^A[17/Jan/2011:18:37:27 +0800]^A200^A55^A7rd65tzq-mida-iptw-wl98-a1c8tji1wm56^1^0^716^^600.0^59.99999999994^65.99999999994^ASID^B10100 |
Output | 10.70.110.94 |
Method | <COLUMN_NAME>.getREGValue(String regexPattern, int groupIndex) |
Return | String |
Usage | FormulaField1 = DATA_REG. getREGValue (“(b(?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)((?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)(d+)(^A)([[^]]*])(^A)(200|500)(^A)(d+)(^A)([a-zA-Z0-9-_]+)(^A?)(1|0|-)(^A?)(-|d+)(^(d+)?)(^(d+)?)(^(d+(.d+))?)(^(d+(.d+))?)(^(d+(.d+))?)(^ASID^B(d+))”, 7); |
Input | 10.70.110.94^A10.71.173.21^A9077^A[17/Jan/2011:18:37:27 +0800]^A200^A55^A7rd65tzq-mida-iptw-wl98-a1c8tji1wm56^1^0^716^^600.0^59.99999999994^65.99999999994^ASID^B10100 |
Output | 17/Jan/2011:18:37:27 +0800 |
Method | <COLUMN_NAME>.getREGValue(String regexPattern, int groupIndex, String defaultValue) |
Return | String |
Usage | FormulaField1 = DATA_REG. getREGValue (“(b(?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)((?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)(d+)(^A)([[^]]*])(^A)(200|500)(^A)(d+)(^A)([a-zA-Z0-9-_]+)(^A?)(1|0|-)(^A?)(-|d+)(^(d+)?)(^(d+)?)(^(d+(.d+))?)(^(d+(.d+))?)(^(d+(.d+))?)(^ASID^B(d+))”,40,”Not Found”); |
Input | 10.70.110.94^A10.71.173.21^A9077^A[17/Jan/2011:18:37:27 +0800]^A200^A55^A7rd65tzq-mida-iptw-wl98-a1c8tji1wm56^1^0^716^^600.0^59.99999999994^65.99999999994^ASID^B10100 |
Output | Not Found |
Method | <COLUMN_NAME>.getREGValues(String regexPattern) |
Return | String array |
Usage | FormulaField1 = DATA_REG. getREGValues (“(b(?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)((?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)(d+)(^A)”); |
Input | 10.70.110.94^A10.71.173.21^A9077^A |
Output | [10.70.110.94,^A, 10.71.173.21,^A,9077,^A] |
Method | <COLUMN_NAME>.getREGValues(String regexPattern, String groupIndexes) |
Return | String array |
Usage | FormulaField1 = DATA_REG. getREGValues (“(b(?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)((?:[0-9]{1,3}.){3}[0-9]{1,3})(^A)(d+)(^A)”,”1,3,5”); |
Input | 10.70.110.94^A10.71.173.21^A9077^A |
Output | [10.70.110.94, 10.71.173.21,9077] |
Fields with Fixed Length Strings data
Method | <COLUMN_NAME>.getFLSValue (String fixedLengthPattern) |
Return | String |
Usage | FormulaField1 = DATA_FLS. getFLSValue (2-3,8-11); |
Input | 1Mr29USJohn |
Output | MrJohn |
Method | <COLUMN_NAME>.getFLSValue (String fixedLengthPattern, String defaultValue) |
Return | String |
Usage | FormulaField1 = DATA_FLS. getFLSValue (14-17, “Not Found”); |
Input | 1Mr29USJohn |
Output | Not Found |
Method | <COLUMN_NAME>.getFLSValues (String fixedLengthPattern) |
Return | String array |
Usage | FormulaField1 = DATA_FLS. getFLSValues (-1:2-3,8-11:4-5:6-7); |
Input | 1Mr29USJohn |
Output | [1,MrJohn,29,US] |
Related content
Copyright Kyvos, Inc. All rights reserved.