Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi John,
Check the attached qvw to see if this solves your problem.
Hope it helps.
Regards,
Carlos
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)
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
I'd rather have the change in the data model. Thanks.
Hi John,
Check the attached qvw to see if this solves your problem.
Hope it helps.
Regards,
Carlos
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
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'