Transfer data from field1 to field2 during load

[#1]

Hello,

I have a table like the following:

Name	Hours	Kg
George	3	150
John	1	175
Mike	8	220
Mike	4	134
George	3	215
John	4	100

I use the load statement

LOAD

          Name,

          Hours,

          Kg

FROM (biff, embedded labels, table is [Sheet1$]);

I would like to change the load in such a way, that whenever the name is John, the Hours get transferred to George's Hours, but the Kg stay with John. Therefore, the internal table should resemble the following:

Name	Hours	Kg
George	3	150
George	1	0
John	0	175
Mike	8	220
Mike	4	134
George	3	215
George	4	0
John	0	100

Do you have any idea of how to accomplish this?

Thanks,

--John

[#2]

To clarify further, I don't care how the internal table would look like. I am only interested in getting the following correct that is:

for George: sum(Hours)=11, sum(Kg)=365

for John: sum(Hours)=0, sum(Kg)=275

for Mike: sum(Hours)=12, sum(Kg)=354)

[IAMDV]

Quick question : Do you want to solve this in the script or using Set Analysis? I mean you keep your data model in the original form and we can work on building the expression instead of changing the script.

Thanks,

DV

[#4]

I'd rather have the change in the data model. Thanks.

[clisboa_noesis · Accepted solution]

Hi John,

Check the attached qvw to see if this solves your problem.

Hope it helps.

Regards,

Carlos

[IAMDV]

Sure, no problem. Here is the attachment. I hope I have understood your requirement.

Please let me know if you have any questions.

Good luck!

Cheers,

DV

http://QlikShare.com

[#7]

Thank you all!

One solution that I implemented myself as well:

LOAD

          'George' as Name,

          Hours

FROM [test1.xls] (biff, embedded labels, table is [Sheet1$])

Where Name='John';

outer join

LOAD

          Name,

          '0' as Hours,

          Kg

FROM [test1.xls] (biff, embedded labels, table is [Sheet1$])

Where Name='John';

outer join

load

          Name,

          Hours,

          Kg

from [test1.xls] (biff, embedded labels, table is [Sheet1$])

where Name <> 'John'