topic Re: How to tag Orders with YES/NO if customer name is same? in QlikView

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

Thu, 16 Jun 2016 08:34:34 GMT

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

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

bindu_apte — Thu, 16 Jun 2016 08:43:30 GMT

Hi,

Can you please provide qvw file

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

Thu, 16 Jun 2016 08:57:59 GMT

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

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

antoniotiman — Thu, 16 Jun 2016 09:16:14 GMT

Try like this (Expression)

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

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

muthukumar77 — Thu, 16 Jun 2016 09:17:27 GMT

Hi,

I hope this sample app will be useful for you.

PFA

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

Thu, 16 Jun 2016 09:22:26 GMT

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

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

Thu, 16 Jun 2016 09:27:18 GMT

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

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

antoniotiman — Thu, 16 Jun 2016 09:34:47 GMT

In Expression

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

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

muthukumar77 — Thu, 16 Jun 2016 09:35:25 GMT

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

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

Thu, 16 Jun 2016 09:43:13 GMT

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)

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

Thu, 16 Jun 2016 09:49:43 GMT

Thank you Antonio it worked !

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

Wed, 20 Jul 2016 09:49:02 GMT

Hi Antonio,

How can I get the count of this 'Yes' and 'No'

I would like to count how many orders are Dual and how many are not dual

I tried following based on the answer to this post's question..

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

But I am getting incorrect total

Thanks

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

antoniotiman — Wed, 20 Jul 2016 10:03:40 GMT

Try

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