Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Transfer data from field1 to field2 during load

Hello,

I have a table like the following:

NameHoursKg
George3150
John1175
Mike8220
Mike4134
George3215
John4100

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:

NameHoursKg
George3150
George10
John0175
Mike8220
Mike4134
George3215
George40
John0100

Do you have any idea of how to accomplish this?

Thanks,

--John

1 Solution

Accepted Solutions
clisboa_noesis
Contributor

Re: Transfer data from field1 to field2 during load

Hi John,

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

Hope it helps.

Regards,

Carlos

6 Replies
Not applicable

Transfer data from field1 to field2 during load

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

Re: Transfer data from field1 to field2 during load

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

Not applicable

Transfer data from field1 to field2 during load

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

clisboa_noesis
Contributor

Re: Transfer data from field1 to field2 during load

Hi John,

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

Hope it helps.

Regards,

Carlos

IAMDV
Honored Contributor II

Re: Transfer data from field1 to field2 during load

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

Not applicable

Transfer data from field1 to field2 during load

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'

Community Browser