Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table as follows:
ID | Emp ID | Emp Name | Sales |
---|---|---|---|
1 | 1000 | John | 100 |
2 | 1001 | Peter | |
3 | 1002 | Mark | |
4 | 1000 | John | |
5 | 1001 | Peter | 1000 |
6 | 1002 | Mark | 1000 |
7 | 1000 | John | 500 |
I want to create a table which will display only the 'Emp Name' which has a value in the field 'Sales'. Basically i want the output as follows:
Emp Name | Sales |
---|---|
John | 100 |
Peter | 1000 |
Mark | 1000 |
John | 500 |
Your help is much appreciated. Thanks.
Create a table, add Emp Name, add sum(Sales), be sure to select "suppress zero values".
Since this sounds like the default functionality, I wonder if I understood your question correct.
Hi,
as suggested by Onno this is default behaviour of qlikview,
but if you want two row of John
then you have to add ID also in your table because Sum(Sales) gives your 600 and single row
Regards
Hi Ramya,
Add a Where clause while loading the data like " WHERE(not IsNull(Sales)); " .
For example:
Employee:
LOAD ID,
EmpName,
Sales
FROM
<Location or Path or Database>
WHERE(not IsNull(Sales));
Hope this helps you.
Thanks,
Harika
Ok actually i already have a calculated dimension for this table. The actual problem is i have a table 'Departments' and i have added a calculated dimension on the table to display only those departments which belong to a particular region. And then i want to add this dimension to display employees who have a value in Sales and belong to the Department to the particular region.
Once i add the calculated dimension to display employees of a Department say 'X' it is displaying all the employees of Department X. I am not able to display only the employees of this Department X with the value in 'Sales' field.
Thanks for your help, i should have been more elaborate on the question.
Hi,
Can you post sample apps which show your problem.
Regards
I suggest not to use Calculated Dimensions at all. Better make those fields in script as that is better for performance.
Can you provide the script or sample data to understamd your problem.
Request you to write the script and not to attach the app as I don't have a liscensed version..working on personal edition.
I may not be able to post my script. But my Department and Employee_Sales tables are as follows:
Department ID | Department Name | Region |
---|---|---|
1 | Sales | NA |
2 | Engineering | SA |
3 | Marketing | NA |
4 | Admin | SA |
5 | HR | NA |
6 | Operations | SA |
7 | IT | NA |
ID | Employee ID | Emp Name | Department ID | Sales |
---|---|---|---|---|
1 | 10000 | John | 1 | 100 |
2 | 10001 | Peter | 1 | |
3 | 10002 | Mark | 1 | 100 |
4 | 10003 | Jack | 2 | 100 |
5 | 10004 | Mary | 3 | 300 |
6 | 10005 | Irina | 4 | 200 |
7 | 10006 | Amy | 4 | 200 |
8 | 10007 | Rich | 5 | |
9 | 10008 | James | 6 | 500 |
10 | 10009 | Pierre | 7 | 600 |
11 | 10010 | Shaun | 7 | 100 |
12 | 10011 | Susan | 7 |
I want the straight table to display data of only those employees who belong to the department of region 'NA' and have data in the 'Sales' field. My output should be as follows:
Department Name | Employee Name | Sales |
---|---|---|
Sales | John | 100 |
Sales | Mark | 100 |
Marketing | Mary | 300 |
IT | Pierre | 600 |
IT | Susan | 100 |
This is the exact problem statement. Your help is much appreciated, Thanks.
I want to do this in the expressions. I cannot modify the load script for now.
Thanks.