topic Re: Load Script - Data Manipulation in QlikView

Load Script - Data Manipulation

hkg_qlik — Tue, 21 Aug 2012 14:00:30 GMT

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,

Re: Load Script - Data Manipulation

Anonymous — Tue, 21 Aug 2012 14:07:27 GMT

which field you want to merge ?

Re: Load Script - Data Manipulation

hkg_qlik — Tue, 21 Aug 2012 14:18:26 GMT

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,

Re: Load Script - Data Manipulation

rwunderlich — Tue, 21 Aug 2012 14:53:26 GMT

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

;

Re: Load Script - Data Manipulation

hkg_qlik — Tue, 21 Aug 2012 15:07:54 GMT

Thanks Rob.

Regards,