Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hkg_qlik
Contributor II

Load Script - Data Manipulation

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

1 Solution

Accepted Solutions

Re: Load Script - Data Manipulation

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

;

4 Replies
srikanthsri1
Valued Contributor

Re: Load Script - Data Manipulation

which field you want to merge ?

hkg_qlik
Contributor II

Re: Load Script - Data Manipulation

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

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
Contributor II

Re: Load Script - Data Manipulation

Thanks Rob.

Regards,

H

Community Browser