I have a requirement of finding New Customers added Quarter Wise.
I have data from 2019 till present. FYear is considered from Apr-till March.
Suppose, if i select Current Financial Year as 2020/2021, then i need to check if the customer_ids from Apr 2019-till present August 2020, i.e.,the Quarters Q1,Q2,Q3 and Q4 of 2019 and the present Quarter of 2020 i.e,Q1 and Q2, is there any transaction done by the customer_ids. If there is no transaction done in all this Quarters, like if it is equal to 0 , then that customer_id will be considered as "New Customer". If Transaction is made in any of the Quarter and customer_id already exists, then it must be considered as "Existing Customer".
I tried to create a flag in the script with below logic,
Transaction_table:
Load
customer_id,
amount,
date
from trans_table;
temp:
load *,
if(customer_id=previous(customer_id),'Existing Customer','New Customer') as Flag
resident Transaction_table order by customer_id,date;
drop table Transaction_table ;
But how can i show this in front End in the chart Quarter Wise.
This is basically finding the duplicates in Customer_id. I tried below, but when we select New Customer, it is also including the customer_id those are existing. In my case, the New Customer must not include the Existing Customer_ids.
In below example, id 2 is repeated. So 2 must be existing customer, but when new customer flag is selected, this id 2 must be excluded. But this is included in New Customers. I have attached the screenshot for reference.
Table:
LOAD * INLINE [
id, date, phone
1, 1/1/2017, a
2, 2/1/2017, d
3, 4/1/2017, a
4, 5/1/2017, a
2, 1/1/2017, d
6,12/1/2017, e
];
FinalTable:
LOAD *,
If(id = Previous(id), 'Existing customer', 'New Customer') as Flag
