Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
In Expression
If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1,'YES','NO')
Hi,
Can you please provide qvw file
Hi bindu.apte Thank you for taking time. I have now attached QVW.
Try like this (Expression)
If(Count(TOTAL <CustomerId,OrderDate,StoreName> OrderId) > 1,'YES','NO')
Hi,
I hope this sample app will be useful for you.
PFA
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
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
In Expression
If(Count(TOTAL <Name,OrderDate,StoreName> OrderId) > 1,'YES','NO')
Sorry, I'm not understood. Can you send me actual output?
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)