Load Script - Data Manipulation

[hkg_qlik]

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 Name	Child Last Name	Child ID	Parent First Name	Parent Last Name	Parent ID
Joe	Flee	123	Alex	Flee	1000
Joe	Flee	123	Ashley	Flee	2000
Alex	Wright	987	Fox	Wright	3000
Alex	Wright	987	Ash	Wright	4000

The End Result should look like this:
Child First Name	Child Last Name	Child ID	Parent 1 First Name	Parent 1 Last Name	Parent 1 ID	Paren 2 First Name	Parent 2 Last Name	Parent 2 ID
Joe	Flee	123	Alex	Flee	1000	Ashley	Flee	2000
Alex	Wright	987	Fox	Wright	3000	Ash	Wright	4000

Regards,

H

[Anonymous]

which field you want to merge ?

[hkg_qlik]

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

[rwunderlich · Accepted solution]

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

;

[hkg_qlik]

Thanks Rob.

Regards,

H