topic Expand a single row into multiple rows based on a specific field value? in App Development

Expand a single row into multiple rows based on a specific field value?

prayner — Tue, 15 Oct 2024 18:18:34 GMT

Let's say:
- I have a table with rows containing 'EU-27' in a field 'country'
- I need to create separate rows for each of the 27 EU countries
- The result should be 27 individual country rows for each original 'EU-27' row

Here is my solution using a simple example, but I am wondering if there is a better approach to do it given that it is a common data prep task:

// Create a sample dataset using inline data MainTable: LOAD * INLINE [ country, value UK, 1 France, 2 USA, 8 ]; // Create a mapping table for UK countries UKCountries: LOAD * INLINE [ uk_nations Wales England Northern Ireland Scotland ]; // Now, create the final table with duplicated UK rows FinalTable: NoConcatenate LOAD country, value RESIDENT MainTable WHERE country <> 'UK' ; for each vCountry in FieldValueList('uk_nations') CONCATENATE (FinalTable) LOAD '$(vCountry)' as country, value RESIDENT MainTable WHERE country = 'UK' ; next vCountry drop table MainTable;

Re: Expand a single row into multiple rows based on a specific field value?

Kushal_Chawda — Tue, 15 Oct 2024 19:12:45 GMT

@prayner you can do this using simple join instead of running loop

MainTable:
LOAD * INLINE [
country, value
UK, 1
France, 2
USA, 8
];

Left Join(MainTable)
// Create a mapping table for UK countries
LOAD * INLINE [
country,uk_nations
UK,Wales
UK,England
UK,Northern Ireland
UK,Scotland
];

Final:
Load *,
if(len(trim(uk_nations))=0,country,uk_nations) as Country_final
Resident MainTable;

Drop Table MainTable;

Drop Fields country,uk_nations;

Re: Expand a single row into multiple rows based on a specific field value?

seanbruton — Tue, 15 Oct 2024 23:34:21 GMT

Kudos nice solution!!

Re: Expand a single row into multiple rows based on a specific field value?

prayner — Wed, 16 Oct 2024 07:16:48 GMT

Nice solution and thanks for sharing, Kushal