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

[#1]

Hi Guys,

I have requirement where I would like to tag the Order as dual Order if the following condition is matched

Condition :

If a Customer placing Order from same Store more then once on the same date then that order should be tagged as dual order.

As an example:

in below table, only Order ID 1 & 2 should be tagged as a Dual Order Because Order ID 3 has different order date.

OrderID	Customer Name	Product Name	Store Name	Order Date
1	Jon Snow	Bread	City Store	15/06/2016
2	Jon Snow	Milk	City Store	15/06/2016
3	Jon Snow	Sugar	Eastern Store	16/06/2016

What I am trying to Achieve:

I want to create a Straight table in QlikView which will have dimensions like (Customer Name, City, DOB, Product, Order Date etc..)

And I want to have an extra column 'Dual Product' and it should be YES or NO like below:

OrderID	Customer Name	Product Name	Store Name	Order Date	Dual Product?
1	Jon Snow	Bread	City Store	15/06/2016	Yes
2	Jon Snow	Milk	City Store	15/06/2016	Yes
3	Jon Snow	Sugar	Eastern Store	16/06/2016	No

My Data Model is like This.. (QVW Attached)

Hope it makes sense.

Thank you

[bindu_apte]

Hi,

Can you please provide qvw file

[#3]

Hi bindu.apte‌ Thank you for taking time. I have now attached QVW.

[antoniotiman]

Try like this (Expression)

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

[muthukumar77]

Hi,

I hope this sample app will be useful for you.

PFA

[#6]

Thank you Muthukumar,

I forgot to mention that every order creates a new customer ID so what should be the load script in that case?

Thanks

[#7]

Hi Antonio, Thank you for your reply but using your expression it shows me all the order placed by Jon Snow as YES

Only Order ID 1 & 2 should be YES because Order Id 3 has different date then 1&2

[antoniotiman · Accepted solution]

In Expression

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

[muthukumar77]

Sorry, I'm not understood. Can you send me actual output?

[#10]

Hi Muthukumar,

Your solution is only tagging Order ID 1,2 & 6 like below

But Order ID 3, 4, 5 should also display value as 'NO'

Also note that every order creates a new customer entry so the customer name Jon Snow will have 3 entries in the customer table. (I have attached the QVW with updated customer table in load script)