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

[prayner]

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;

[Kushal_Chawda · Accepted solution]

@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;

[seanbruton]

Kudos nice solution!!

[prayner]

Nice solution and thanks for sharing, Kushal