Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community, I'm having a problem with some data that I need to add on the load section, but I'm not sure if it can even be done.
The data I have is like this:
Name | Activity | Hours |
Dave | A | 2 |
Dave | B | 2 |
Dave | C | 2 |
Peter | A | 3 |
Peter | B | 2 |
The thing is that i need to create a new column that contains the adding number, like this:
Name | Activity | Hours | Total Hours |
Dave | A | 2 | 6 |
Dave | B | 2 | 6 |
Dave | C | 2 | 6 |
Peter | A | 3 | 5 |
Peter | B | 2 | 5 |
So Dave has 6 Hours and Peter 5.
Is there a way to add the numbers on the loading section?
The problem is that this information comes from one set of data, I have two more that comes with the columns with the total hours.
Thanks alot
You sure can:
Table:
LOAD Name,
Activity,
Hours
FROM
[https://community.qlik.com/thread/198409]
(html, codepage is 1252, embedded labels, table is @1);
Join (Table)
LOAD Name,
Sum(Hours) as [Total Hours]
Resident Table
Group By Name;
Hi Francisco, you can add the TotalHours with a left join. As an example:
Data:
LOAD * Inline [
Name,Activity,Hours
Dave,A,2
Dave,B,2
Dave,C,2
Peter,A,3
Peter,B,2
];
Left join (Data)
LOAD Name, Sum(Hours) as TotalHours Resident Data Group By Name;
Returns:
I don't understand the problem in your last sentence, not sure if you need something more.
Hi Francisco,
Data:
LOAD Name,
Activity,
Hours
FROM.....
Left Join (Data)
LOAD Name,
Sum(Hours) as [Total Hours]
Resident Data
Group By Name;
Please elaborate on your last requirement,is total hours column in another table or do you have other columns(in other table or data set) we need to include in calculations.
Regards
Neetha
Its another column in another table
What is another column in another table??
please specify about other column in other table, so can help.
Sorry, what I wanted to say is that, the originals data comes from a file, then from that file i want to create another one that has the same information but also the new column that is "Total Hours".
add a store to the end of Sunny's code; Table.txt is the new file with the new column Total Hours (see image)
STORE Table into Table.txt (txt);