count based on other columns

[dineshkumar]

Hi All,

I'm having two tables

Instance:

if i count complaint no based on login as a dimension and filter as sand and oil,, the output will be

Actual output:

scenario:

but i want oil_filter, sand_fliter to display count(complaint_no) based on assigned_login for login dimension.

expected output:

For Your reference attached is the same file.

Thanks,

Dinesh.

[sunny_talwar]

Do you need the two tables to be linked by complaint_no? I feel that as long as the two tables are connected, this might not be possible, but I could be wrong.

[adamdavi3s]

I thought this should have worked...but maybe not, maybe it can't be that clever / simple!

count({<Login=P({1}Assigned_user),reduction_field={'oil'}>}complaint_no)

[#4]

What is the relation to Assigned_Login and Login ?

[vinieme12]

I don't think you need the 1st table , moreover the data sample seems  inaccurate

Example:

table1:

LoginID = 1 , you have a, aa as Complaint numbers

but table2:

LoginID=1, you have c,d,e as complaint number? 

Are both these complaints numbers un-related?

If your count needs to be based on LoginID, then link by Login ID.

[dineshkumar]

both are same, case is like login will create complaint_no,, but assigned_login will pick that compalint_no and proceeds.. so i want count based on assigned_login for oil_filter bcz they are the person who has processed it.

[vinieme12]

break the link, using fields from only table 2, you get below result

[dineshkumar]

Login creates the case and assigned_login picks the case and process it, so count of complaints processed by login is required. (login and ass_log are same)

[adamdavi3s]

I would be re-working the data model to be honest. Appreciate this is probably a simple example of what you're trying to do but this will give you the right answer

INLINE_1:

LOAD *, 'IL1'&rowno() as key1 INLINE [

    Login , complaint_no1

    1    , a

    1    , aa

    2    , b

    2    , c

    2    , d

    3    , e

    3    , f

    3    , g

];

INLINE_2:

load  *, 'IL2'&rowno() as key2 inline [

complaint_no2,Assigned_login,reduction_field

a,          2,            oil

b,          2,            oil

c,          1,            oil

d,          1,            sand

e,          1,            sand

f,          3,            sand

g,          3,            oil

aa,          2,            oil

];

INLINE_3:

Load Login as user, key1, complaint_no1 as complaint_no RESIDENT INLINE_1;

Concatenate

Load Assigned_login as user, key2, complaint_no2 as complaint_no RESIDENT INLINE_2;

[vinieme12]

but your login&complaint ID's are Mismatched in both tables!