topic How can I build a table about how many times a given value has a common feature with others? in QlikView

How can I build a table about how many times a given value has a common feature with others?

mlarruda — Wed, 25 Nov 2020 16:16:04 GMT

Well, I have a table like this:

Day of Work	Worker
Monday	Adam
Monday	Bob
Monday	Carl
Tuesday	Adam
Tuesday	Carl
Tuesday	Dan
Tuesday	Emma
Wednesday	Bob
Wednesday	Carl
Wednesday	Dan
Thursday	Bob
Thursday	Emma
Thursday	Fred
Friday	Bob
Friday	Carl
Friday	Emma
Friday	Fred

And I want to know how many days each Worker worked with Adam, i. e., I want to obtain a table like that:

Worker	Days
Bob	1
Carl	2
Dan	1
Emma	2
Fred	1

Anyone can help me? Many thanks in advance.

Re: How can I build a table about how many times a given value has a common feature with others?

MK_QSL — Wed, 28 Mar 2018 20:36:27 GMT

Do you want to achieve this at script level or at front end?

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Wed, 28 Mar 2018 20:43:20 GMT

Sorry, I don't know what is Front End, but I wished something I could use directly in the table building.

However, if this is possible only in the same script i use to read the data, then I can go for it.

Re: How can I build a table about how many times a given value has a common feature with others?

MK_QSL — Wed, 28 Mar 2018 20:47:33 GMT

How Emma has worked 2 days with Adam?

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Wed, 28 Mar 2018 20:52:49 GMT

Sorry, in copying the table from Excel something went wrong. The correct output table would be:

Bob - 1

Carl - 2

Dan - 1

Emma - 1

Re: How can I build a table about how many times a given value has a common feature with others?

MK_QSL — Wed, 28 Mar 2018 20:54:48 GMT

Create a Straight Table

Dimension

Worker

Expression

Count({<Worker -= {'Adam'},[Day of Work] = P({1<Worker = {'Adam'}>}[Day of Work])>}Distinct [Day of Work])

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Mon, 02 Apr 2018 14:19:45 GMT

WORKED, with a small correction:

Count({<Worker -= {'Adam'},[Day of Work] = P({1<Worker = {'Adam'}>}[Day of Work])>}Distinct [Day of Work])

This 1, after "P({", seems to be a type error.

Thanks a lot!

Re: How can I build a table about how many times a given value has a common feature with others?

MK_QSL — Mon, 02 Apr 2018 18:50:56 GMT

This is not a type error...

1 will make sure that when something selected then the inner set expression will ignore that selection and you will still get Day or Work for Adam.

Also, if you got your answer then kindly close the thread by selecting correct answer.

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Mon, 02 Apr 2018 19:04:46 GMT

Thank you, but for me the script didn't work with this 1.

Re: How can I build a table about how many times a given value has a common feature with others?

MK_QSL — Tue, 03 Apr 2018 08:59:53 GMT

Please check that you have write it correctly and no syntax error !!

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Tue, 03 Apr 2018 14:20:02 GMT

I tested and it worked both with and without the "1" but still has some problems: my real database has thousands of registers and a bit more variables. And, for example, Adam has worked 417 times with Ben in 2017 and 5 times with Carl in 2016.

So, when I filter by clicking in year = 2016, the table shows Carl and 5.

When I filter by clicking in year = 2017, the table shows Ben and 417.

BUT, when I do not filter (and, so, am considering all years), the table shows only Carl and 5 instead of both rows. Can you say me why is it happening?

Re: How can I build a table about how many times a given value has a common feature with others?

MK_QSL — Tue, 03 Apr 2018 15:48:20 GMT

Provide exact expression which you are using in your actual application.

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Tue, 03 Apr 2018 15:58:39 GMT

Count({<NAME -= {'Adam'},WORKDAY = P({1<NAME = {'Adam'}>}WORKDAY)>}Distinct WORKDAY)

Re: How can I build a table about how many times a given value has a common feature with others?

MK_QSL — Tue, 03 Apr 2018 19:22:10 GMT

Can you provide sample app or sample data along with Year so that I can work on it?

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Tue, 03 Apr 2018 21:00:16 GMT

Sure. Here follows a sample data of 32 rows:

YEAR	WORKDAY	NAME
2017	Day01	Adam
2017	Day01	Ben
2017	Day02	Adam
2017	Day02	Ben
2016	Day03	Adam
2016	Day03	Ben
2016	Day04	Adam
2016	Day04	Ben
2017	Day05	Adam
2017	Day05	Ben
2016	Day06	Adam
2016	Day07	Adam
2016	Day07	Ben
2016	Day08	Adam
2016	Day08	Ben
2016	Day09	Adam
2016	Day10	Adam
2016	Day10	Carl
2016	Day11	Ben
2016	Day12	Ben
2016	Day13	Ben
2016	Day14	Ben
2016	Day15	Ben
2016	Day16	Ben
2016	Day17	Ben
2016	Day18	Carl
2017	Day19	Carl
2017	Day20	Carl
2017	Day21	Carl
2017	Day22	Carl
2017	Day23	Carl
2017	Day24	Carl

As you can see, Adam worked:

* Three days with Ben in 2017 (Days 01, 02 and 05)

* Four days with Ben in 2016 (Days 03, 04, 07 and 08)

* One day with Carl in 2016 (Day 10)

I'm using the formula

Count({<NAME -= {'Adam'},WORKDAY = P({1<NAME = {'Adam'}>}WORKDAY)>}Distinct WORKDAY)

And getting the following results:

Many thanks in advance for any solution you can find.

Re: How can I build a table about how many times a given value has a common feature with others?

MK_QSL — Wed, 04 Apr 2018 08:11:41 GMT

Let me revise what you want?

For your sample data..

When you select 2016, you need Ben 4 and Carl 1

When you select 2017, you need Ben 3

When you not select any year, Ben 7 and Carl 1

Correct me if I am wrong !!

if this is what you need,

use below expression

Count({<NAME -= {'Adam'},WORKDAY = P({1<NAME = {'Adam'}>}WORKDAY)>}Distinct WORKDAY)

Re: How can I build a table about how many times a given value has a common feature with others?

MK_QSL — Wed, 04 Apr 2018 08:13:43 GMT

QVW attached for your reference...

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Wed, 04 Apr 2018 14:01:42 GMT

Thanks! It is exactly what I want, but this expression is the same I am already using and that generated the screens above.

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Wed, 04 Apr 2018 14:12:31 GMT

Sorry, but I can't open this qvw:

Google translation: "This QlikView document was created by another QlikView Personal Edition user. Since you are using QlikView Personal Edition, you can only open files that you have created yourself. You can recover the file if you have created it on another computer, but doing so will use one of your remaining 4 recovery attempts.

However, if you recover the file, you can no longer open files created with your current user key.

Do you want to continue and recover the file?"

Re: How can I build a table about how many times a given value has a common feature with others?

mlarruda — Wed, 04 Apr 2018 14:22:10 GMT

Well, I solved the problem. It was a big silliness of me.

Note that, in the screens I posted before, the resulting table has (I don't know why) only one row. But now I noted it and dragged the bottom margin, showing the 2nd row, showing the correct numbers.

So, many thanks for all and sorry for my silliness.