Multiple Records from  single field concatenated data (subfield)

    I think this has been covered elsewhere in the community but its a nice little feature and worth highlighting again.

     

    Sometimes data is used which has multiple values in a single field. A classic example being a web form submission where the site visitor has made multiple selections e.g.

     

    Client, Departments

    AV,Finance;IT;Marketing;Sales;HR

     

    From an analysis perspective, it could be useful to have this broken down into multiple records thus

     

    Client, Department

    AV, Finance

    AV, IT

    AV, Marketing

    AV, Sales

    AV, HR


    Subfield is the function to use. In this case the formula to use in the load script  would be


    subfield(Departments ',') as Department


    This would generate 5 records from 1. In the attached example, the source data;

    subfield.JPG

    is converted into


    subfeld2.JPG