Contents:
Your source column (MyKeyValues) is formatted in the following manner:
key1=value1,key2=value2
Basic Usage
The following transform extracts the key-value pairs. The key parameter contains a single pattern that matches all keys that you want to extract:
extractkv col: MyKeyValues key:`{alpha}+{digit}` valueafter: '=' delimiter: ','
Output: The generated column contains data that looks like the following:
{"key1":"value1","key2":"value2"}
If the source data contained additional keys which were not specified in the transform, those key-value pairs would not appear in the generated column.
Syntax and Parameters
extractkv col:column_ref delimiter:string_literal_pattern key:string_literal_pattern valueafter:string_literal_pattern [as:'new_column_name']
| Parameter | Required? | Data Type | Description |
|---|---|---|---|
| extractkv | Y | transform | Name of the transform |
| col | Y | string | Source column name |
| delimiter | Y | string | String literal or pattern that identifies the separator between key-value pairs |
| key | Y | string | Pattern that identifies the key to match |
| valueafter | Y | string | String literal or pattern after which is located a key's value |
| as | N | string | Name of the newly generated column |
For more information on syntax standards, see Language Documentation Syntax Notes.
col
Identifies the column to which to apply the transform. You can specify only one column.
Usage Notes:
| Required? | Data Type |
|---|---|
| Yes | String (column name) |
delimiter
In the following:
{ key1=value1,key2=value2 }
The delimiter is the comma ( ','). The final key-value pair does not need a delimiter.
Tip: You can insert the Unicode equivalent character for this parameter value using a regular expression of the form /\uHHHH/. For example, /\u0013/ represents Unicode character 0013 (carriage return). For more information, see Supported Special Regular Expression Characters.
Usage Notes:
| Required? | Data Type |
|---|---|
| Yes | String (literal, regular expression, or Alteryx pattern) |
key
extractkv transform. For the following data:{ key1=value1,key2=value2 }
The keys are represented in the transform by the following parameter and value:
key:`{alpha}+{digit}`
This pattern matches all keys that begin with a letter and end with a digit. If the source data contains other keys, they do not appear in the extracted data.
Usage Notes:
| Required? | Data Type |
|---|---|
| Yes | Single pattern representing the individual keys to extract. |
valueafter
For the following:
{ key1=value1,key2=value2 }
The valueafter string is the equals sign ( '=').
Usage Notes:
| Required? | Data Type |
|---|---|
| Yes | String (literal, regular expression, or Alteryx pattern) |
as
as parameter is not specified, a default name is used.
Usage Notes:
| Required? | Data Type |
|---|---|
| No | String (column name) |
Tip: For additional examples, see Common Tasks.
Examples
Example - extracting key values from car data and the unnesting into separate columns
- extractkv - Removes key-value pairs from a source string. See Extract Transform.
unnest- Unpacks nested data in separate rows and columns. See Unnest Transform.
Source:
You have the following information on used cars. The VIN column contains vehicle identifiers, and the Properties column contains key-value pairs describing characteristics of each vehicle. You want to unpack this data into separate columns.
| VIN | Properties |
|---|---|
| XX3 JT4522 | year=2004,make=Subaru,model=Impreza,color=green,mileage=125422,cost=3199 |
| HT4 UJ9122 | year=2006,make=VW,model=Passat,color=silver,mileage=102941,cost=4599 |
| KC2 WZ9231 | year=2009,make=GMC,model=Yukon,color=black,mileage=68213,cost=12899 |
| LL8 UH4921 | year=2011,make=BMW,model=328i,color=brown,mileage=57212,cost=16999 |
Transform:
Add the following transform, which identifies all of the key values in the column as beginning with alphabetical characters.
- The
valueafterstring identifies where the corresponding value begins after the key. - The
delimiterstring indicates the end of each key-value pair.
extractkv col:Properties key:`{alpha}+` valueafter:`=` delimiter:`,`
unnest transform to unpack this mapped data. In the following, each key is specified, which results in separate columns headed by the named key:
unnest col:extractkv_Properties keys:'year','make','model','color','mileage','cost'
When you drop the unnecessary Properties columns, the dataset now looks like the following:
| VIN | year | make | model | color | mileage | cost |
|---|---|---|---|---|---|---|
| XX3 JT4522 | 2004 | Subaru | Impreza | green | 125422 | 3199 |
| HT4 UJ9122 | 2006 | VW | Passat | silver | 102941 | 4599 |
| KC2 WZ9231 | 2009 | GMC | Yukon | black | 68213 | 12899 |
| LL8 UH4921 | 2011 | BMW | 328i | brown | 57212 | 16999 |
This page has no comments.