Can we transpose a dataset using ECL language?

Question

First Question : I want to transpose a data given below as an example : (https://i.stack.imgur.com/i5v8p.png) I need to reshape this data by transposing it to get output as : (https://i.stack.imgur.com/wwmAl.png)

Second Question :

Taking the same example. Considering the below one as my input : (https://i.stack.imgur.com/wwmAl.png)

Output needs to have format as shown below : (https://i.stack.imgur.com/wDkZu.png) Here we need to read field name. I am not sure whether ecl provides a library to do so or whether it should be hard coded.

I am in need of a sample code to achieve these transform.

Thank you.

Answer 1

What you're asking for is to create a nested child dataset of the address components. Here's your example to do that:

inrec := RECORD
  STRING30 name;
  STRING30 street;
  STRING20 city;
  STRING20 state;
  STRING10 country;
  STRING10 postcode;
END;
inds := DATASET([{'Jaques Cousteau','23, quai de Conti','Paris','','France','75270'},
                 {'Emmy Noether','010 N Merion Avenue','Bryn Mawr','Pennsylvania','USA','19010'}],
                inrec);
inds;

AddrRec := RECORD
  STRING10 AddrPart;
  STRING30 AddrVal;
END;
outrec := RECORD
  STRING30 name;
  DATASET(AddrRec) Address;
END;                
outds := 
    PROJECT(inds,
            TRANSFORM(outrec,
                      SELF.Address := DATASET([{'Street',LEFT.Street},
                                               {'City',LEFT.City},
                                               {'Country',LEFT.Country},
                                               {'PostCode',LEFT.PostCode}],
                                              AddrRec),                
                      SELF := LEFT));                
outds;              

HTH,

Richard