topic Re: How to create a table from key value pairs which are not always present? in Getting Data In

How to create a table from key value pairs which are not always present?

MarietaO — Wed, 18 May 2022 23:08:26 GMT

Hi everyone,

I would like to extract a table. For instance:

SOH is a special character

Input

id=1, message body= [35=DSOH45=CSOH]

id= 2, message body= [35=FSOH53=ASOH45=CSOH]

+10k messages as such

Result

35 45 53

D C

F C A

Create headers with all keys provided through all messages.

If key is not provided on message then cell should be empty.

As you can see 53 is not always present, when that is the case its cell is empty.

Thanks in advance.

Re: How to create a table from key value pairs which are not always present

ITWhisperer — Wed, 18 May 2022 21:08:37 GMT

| rex "35=(?<f35>\w+)SOH" | rex "45=(?<f45>\w+)SOH" | rex "53=(?<f53>\w+)SOH"

Re: How to create a table from key value pairs which are not always present

MarietaO — Thu, 19 May 2022 11:12:45 GMT

The contents of the body field are not indexed. SOH is an invisible character in ASCII.

SOH

The unique keys are unknown.

Body is indexed but not the key value pairs that I would like to transform to a table.

Re: How to create a table from key value pairs which are not always present

MarietaO — Thu, 19 May 2022 11:28:02 GMT

e.g. 8=FIX.4.19=9035=049=INVMGR56=BRKR

Re: How to create a table from key value pairs which are not always present

ITWhisperer — Thu, 19 May 2022 11:53:59 GMT

| rex max_match=0 "(?<_name>\d+)=(?<_value>\w+)\cA" | eval namevalue=mvzip(_name, _value) | mvexpand namevalue | eval _name=mvindex(split(namevalue,","),0) | eval _value=mvindex(split(namevalue,","),1) | eval {_name}=_value | fields - namevalue | stats values(*) as * by _raw

Re: How to create a table from key value pairs which are not always present?

MarietaO — Thu, 19 May 2022 12:38:11 GMT

Works, thanks a lot. And if I want to short by numeric values the columns e.g. asc.

Re: How to create a table from key value pairs which are not always present?

ITWhisperer — Thu, 19 May 2022 13:00:31 GMT

Can you give an example of what you have and what you would like it to be?

Re: How to create a table from key value pairs which are not always present?

MarietaO — Thu, 19 May 2022 13:15:28 GMT

Sure, thanks a lot.

k1=1, k2=3,body=[8=FIX.4.19=9035=049=INVMGR56=BRKR]

k1=1, k2=3, body=[8=FIX.4.11=1035=049=INVMGR58=BR]

many key value pairs in body (not known).

order is numeric on columns.

To export to csv as a table.

1	9	35	49	56	58
	90	0	INVMGR	BRKR
10		0	INVMGR		BR

Re: How to create a table from key value pairs which are not always present?

ITWhisperer — Thu, 19 May 2022 13:22:04 GMT

| transpose 0 header_field=key column_name=key | sort 0 key | transpose 0 header_field=key column_name=key | fields - key

Re: How to create a table from key value pairs which are not always present?

MarietaO — Fri, 20 May 2022 09:42:15 GMT

works perfectly thanks.