Intellicus Enterprise Reporting and Business Insights 19.0

Parsing Data from Complex Fields

0 views June 28, 2020 0

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]