topic Re: How to count repeating values from 3 different columns in QlikView

How to count repeating values from 3 different columns

Wed, 20 Jul 2016 09:40:42 GMT

Hi Guys,

Few months ago I posted a question and I got the write answer.

How to tag Orders with YES/NO if customer name is same?

This question relates to the above mentioned post.

What would be the right expression to count to total orders where following fields are same?

[OrderDate], [Name], [StoreName]

I tried following but I am getting incorrect totals

Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1

Re: How to count repeating values from 3 different columns

danansell42 — Wed, 20 Jul 2016 09:54:08 GMT

Can you share a sample app please

Re: How to count repeating values from 3 different columns

MK_QSL — Wed, 20 Jul 2016 10:05:32 GMT

May be

COUNT(TOTAL <Name, OrderDate, StoreName>DISTINCT OrderID)

Re: How to count repeating values from 3 different columns

Gysbert_Wassenaar — Wed, 20 Jul 2016 10:07:57 GMT

Perhaps like this:

count({<OrderId={"=count(distinct [OrderDate]&'|'& [Name] &'|'& [StoreName]) )<>count([OrderDate]&'|'& [Name] &'|'& [StoreName])"}>}distinct OrderId)

Re: How to count repeating values from 3 different columns

Wed, 20 Jul 2016 10:49:28 GMT

Thank you guys but your suggested solution does not work.

I have attached an example QVD in the post

Can you please try that?

Thank you

Re: How to count repeating values from 3 different columns

effinty2112 — Wed, 20 Jul 2016 10:57:12 GMT

Hi Lokesh,

Try this:

Sum(Aggr(if(Count(OrderId)>1,1,0),Name,OrderDate,StoreName))

good luck

Andrew

Re: How to count repeating values from 3 different columns

Gysbert_Wassenaar — Wed, 20 Jul 2016 10:58:30 GMT

=sum(aggr(if(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1,1,0),OrderId,Name, OrderDate))

Re: How to count repeating values from 3 different columns

sunny_talwar — Wed, 20 Jul 2016 11:03:05 GMT

May be this:

=Count(DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1, OrderId), OrderId, Name, OrderDate))

=Count(DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) = 1, OrderId), OrderId, Name, OrderDate))

Re: How to count repeating values from 3 different columns

Wed, 20 Jul 2016 11:03:15 GMT

Thanks it is working

And where can I modify this expression to get the count of the rest of the orders where those 3 fields are not same.

Re: How to count repeating values from 3 different columns

Wed, 20 Jul 2016 11:04:58 GMT

Thanks mate.

Re: How to count repeating values from 3 different columns

Gysbert_Wassenaar — Wed, 20 Jul 2016 11:05:04 GMT

Subtract it from the total count.

Re: How to count repeating values from 3 different columns

sunny_talwar — Wed, 20 Jul 2016 11:05:20 GMT

No problem at all

Re: How to count repeating values from 3 different columns

Wed, 20 Jul 2016 11:06:37 GMT

Just one more addition in this same question.

Where can I add Set analysis in this expression if I only want those order where [OrderStatus] = 'Open'

Re: How to count repeating values from 3 different columns

sunny_talwar — Wed, 20 Jul 2016 11:09:04 GMT

May be like these:

=Count({<OrderStatus = {'Open'}>} DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> {<OrderStatus = {'Open'}>} OrderId) > 1, OrderId), OrderId, Name, OrderDate))

=Count({<OrderStatus = {'Open'}>} DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> {<OrderStatus = {'Open'}>} OrderId) = 1, OrderId), OrderId, Name, OrderDate))

Re: How to count repeating values from 3 different columns

Wed, 20 Jul 2016 11:14:18 GMT

Thanks a lot.

Re: How to count repeating values from 3 different columns

Thu, 28 Jul 2016 23:17:36 GMT

Hi Sunny,

You expression works perfect for counting repeating Customer Name, Order Date and Store Name.. but where can I add if statement to show 'YES' or 'NO' if those 3 values are repeating or not?

I am currently using following formula

If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1,'YES','NO')

But this formula is not showing right result for every order.

Below is your expression to count.. where can I tweak this to get 'YES' or 'NO' ?

=Count(DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1, OrderId), OrderId, Name, OrderDate))

Re: How to count repeating values from 3 different columns

sunny_talwar — Fri, 29 Jul 2016 05:38:21 GMT

Where exactly are you using this expression? In the straight table?

If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1,'YES','NO')

Seems to be working here, what is the issue?

Re: How to count repeating values from 3 different columns

Fri, 29 Jul 2016 05:42:41 GMT

Hi Sunny, The issue is that I want to count how many of these dual orders have been cancelled? so

So Order ID 1 and 2 are dual orders but if one of them have status = 'Cancelled' then I want to know total how many Orders are cancelled which falls into dual order definition?

Count of both dual order cancelled

count of one of the dual order cancelled

Re: How to count repeating values from 3 different columns

sunny_talwar — Fri, 29 Jul 2016 05:53:43 GMT

In a text box object? May be this

Count of both dual order cancelled

=Count(DISTINCT Aggr(If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1 and SubStringCount(Concat(DISTINCT Status, ','), 'Cancelled') = 1 and Count(DISTINCT Status) = 1, OrderId), OrderId, Name, OrderDate))

count of one of the dual order cancelled