4 Replies Latest reply: Aug 21, 2012 11:07 AM by Hardik Gandhi RSS

    Load Script - Data Manipulation

    Hardik Gandhi

      Hi,

       

      I want to create a load script for the following scenario:

       

      I have a excel spreadsheet with around 1000 records. (File attached)

       

      The file contains two records per child. I want to convert them into one record as shown in the file attached

       

       

      Child First NameChild Last NameChild IDParent First NameParent Last NameParent ID
      JoeFlee123AlexFlee1000
      JoeFlee123AshleyFlee2000
      AlexWright987FoxWright3000
      AlexWright987AshWright4000

       

      The End Result should look like this:












      Child First NameChild Last NameChild IDParent 1 First NameParent 1 Last NameParent 1 IDParen 2 First NameParent 2 Last NameParent 2 ID
      JoeFlee123AlexFlee1000AshleyFlee2000
      AlexWright987FoxWright3000AshWright4000

       

      Regards,

      H

        • Re: Load Script - Data Manipulation
          Raj Vadde

          which field you want to merge ?

            • Re: Load Script - Data Manipulation
              Hardik Gandhi

              If you look at the attached excel file it will help you understand what is the source data and what is the end result I am looking for:

               

              Two records for each child: fields are Child First Name, Child Last Name, Child ID.

               

              I want to create one record for each child and related Parent.

               

              Hope this makes it more clear for you.

               

              Thanks,

              H

            • Re: Load Script - Data Manipulation
              Rob Wunderlich

              Here's an approach.

               

              -Rob

               

              data:

              LOAD Distinct

                        [Child First Name],

                   [Child Last Name],

                   [Child ID]

              FROM

              [Data Set_Example.xlsx]

              (ooxml, embedded labels, table is Sheet1)

              ;

               

              LEFT JOIN (data)

              LOAD

                   [Child ID],

                   [Parent First Name] as [Parent 1 First Name],

                   [Parent Last Name] as [Parent 1 Last Name],

                   [Parent ID] as [Parent 1 ID]

              FROM

              [Data Set_Example.xlsx]

              (ooxml, embedded labels, table is Sheet1)

              WHERE AutoNumber(RecNo(),[Child ID]) =1

              ;

              LEFT JOIN (data)

              LOAD

                   [Child ID],

                   [Parent First Name] as [Parent 2 First Name],

                   [Parent Last Name] as [Parent 2 Last Name],

                   [Parent ID] as [Parent 2 ID]

              FROM

              [Data Set_Example.xlsx]

              (ooxml, embedded labels, table is Sheet1)

              WHERE AutoNumber(RecNo(),[Child ID]) =2

              ;