condition on the dimension field in a table

[#1]

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.

[oknotsen]

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.

[PrashantSangle]

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

[#4]

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

[#5]

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.

[PrashantSangle]

Hi,

Can you post sample apps which show your problem.

Regards

[oknotsen]

I suggest not to use Calculated Dimensions at all. Better make those fields in script as that is better for performance.

[ankitaag]

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.

[#9]

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.

[#10]

I want to do this in the expressions. I cannot modify the load script for now.

Thanks.