Add column filter

The Add column filter on the Recipe page adds a column with a fixed value to the left or right side of a dataset.

If you want to add multiple columns, create multiple steps using this filter.

Options

Tag for new column: the HXL hashtag (and optional attributes) that will appear at the top of the new column, e.g. "Source".

Fixed value for new column: (optional) the value that will appear in every cell of the new column, e.g. "United Nations Development Programme". You can substitute values for other columns by surrounding them in double braces; for example, "X-{{#country+code}}" will include "X-" followed by the value of #country+code in each row (e.g. "X-GIN", "X-SEN", etc). You can also include a full row formula inside the double braces.

Display header for new column: (optional) the text header that will appear above the new HXL hashtag, e.g. "Source".

Add new column to front: if checked, the HXL Proxy will add the new column to the left side of the dataset; if unchecked, it will append it to the right side.

Typical use cases

This filter is mainly useful for making implied information explicit to improve interoperability.

For example, if organisations collect a humanitarian dataset for Burundi, they won't likely have a Country column with "Burundi" in every row underneath, but that information is important if you want to combine it with data from other countries. This filter lets you add that information after the fact.

Other examples of fixed values include a dataset from a single source (e.g. the UNDP), a dataset describing a single organisation (e.g. World Vision), or a dataset applying to a single sector/cluster (e.g. WASH).

Examples

1. Fixed values

Adding the column "#country" with the fixed value "Foolandia":

Before

#org	#sector	#output+activities
Red Cross	Shelter	15
UNICEF	Education	23
MSF	Health	12

After

#org	#sector	#output+activities	#country
Red Cross	Shelter	15	Foolandia
UNICEF	Education	23	Foolandia
MSF	Health	12	Foolandia

2. Using substitutions

Adding the column "#org +fullname" with the value "Foolandia {{#sector}} Cluster", and setting the Add to front flag:

Before

#org	#sector	#output+activities
Red Cross	Shelter	15
UNICEF	Education	23
MSF	Health	12

After

#sector +fullname	#org	#sector	#output+activities
Foolandia Shelter Cluster	Red Cross	Shelter	15
Foolandia Education Cluster	UNICEF	Education	23
Foolandia Health Cluster	MSF	Health	12

3. Using a full formula

Adding the column "#affected +total" with the formula "{{sum(#affected)}}", which will add the values of all columns with a hashtag matching the pattern "#affected" in each row:

Before

#adm1	#affected +f +children	#affected +f +adults	#affected +m +children	#affected +m +adults
Coast Region	203	578	185	318
Mountain Region	518	1024	2193	1722
Plains Region	18	35	13	27

After

#adm1	#affected +f +children	#affected +f +adults	#affected +m +children	#affected +m +adults	#affected+total
Coast Region	203	578	185	318	1284
Mountain Region	518	1024	2193	1722	5457
Plains Region	18	35	13	27	93

            |