Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
;
which field you want to merge ?
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
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
;
Thanks Rob.
Regards,
H