topic Re: count based on other columns in QlikView

count based on other columns

dineshkumar — Wed, 30 Nov 2016 14:14:15 GMT

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.

Re: count based on other columns

sunny_talwar — Wed, 30 Nov 2016 14:21:17 GMT

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.

Re: count based on other columns

adamdavi3s — Wed, 30 Nov 2016 14:21:58 GMT

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)

Re: count based on other columns

Wed, 30 Nov 2016 14:26:02 GMT

What is the relation to Assigned_Login and Login ?

Re: count based on other columns

vinieme12 — Wed, 30 Nov 2016 14:27:21 GMT

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.

Re: count based on other columns

dineshkumar — Wed, 30 Nov 2016 14:29:00 GMT

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.

Re: count based on other columns

vinieme12 — Wed, 30 Nov 2016 14:31:20 GMT

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

Re: count based on other columns

dineshkumar — Wed, 30 Nov 2016 14:31:32 GMT

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)

Re: count based on other columns

adamdavi3s — Wed, 30 Nov 2016 14:33:26 GMT

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 [

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;

Re: count based on other columns

vinieme12 — Wed, 30 Nov 2016 14:37:45 GMT

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

Re: count based on other columns

adamdavi3s — Wed, 30 Nov 2016 14:42:53 GMT

That seems correct to me based on the explanation given?

Re: count based on other columns

dineshkumar — Wed, 30 Nov 2016 15:48:44 GMT

to be practical inline_1 is live table, unique row data gets entered here at live , inline_2 is history table.

when complaint gets created it will be at inline_1 and from then whenevere a case is picked for processing, reverification these data goes to inline_2...

IF , login 1 creates complaint a, the row entered in inline_1, then later when it goes for complaint resolution say login guy 2 picks the case and process it ..

e.g. in inline_1 user is login , later when login picking case is assigned_login in inline_2.

so, login 2 picked the case, hence oil_filter 's count should show in login2 and not who created the record...