topic Re: Transfer data from field1 to field2 during load in QlikView

Transfer data from field1 to field2 during load

Fri, 04 May 2012 07:57:19 GMT

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,

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

Transfer data from field1 to field2 during load

Fri, 04 May 2012 08:08:07 GMT

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)

Re: Transfer data from field1 to field2 during load

IAMDV — Fri, 04 May 2012 11:30:17 GMT

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,

Transfer data from field1 to field2 during load

Fri, 04 May 2012 11:33:00 GMT

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

Re: Transfer data from field1 to field2 during load

clisboa_noesis — Fri, 04 May 2012 12:07:39 GMT

Hi John,

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

Hope it helps.

Regards,

Carlos

Re: Transfer data from field1 to field2 during load

IAMDV — Fri, 04 May 2012 12:13:31 GMT

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,

http://QlikShare.com

Transfer data from field1 to field2 during load

Fri, 04 May 2012 14:53:56 GMT

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,

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

Where Name='John';

outer join

load

Name,

Hours,

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

where Name <> 'John'