Compare rows and Create flag

[sunainapawar]

Hello,

I need to compare the customer_id's in the same table, and create a Flag of values as New and Existing.

I tried below in the script. I tried to compare the customer_id with previous customer_id, if it matches, then it should be 'Existing', If it doesnot matches then it should be new Customer_id. I took this flag in front end but its not working. For both existing and New values there are no matching customer_id's shown.

processed_xp_customer_master:
LOAD
customer_id,
customer_enrolled_on,
customer_enrolled_on & customer_id as key

from customer.xlsx;

load
customer_enrolled_on & customer_id as key,
if((customer_id)=Previous(customer_id),'Existing Customer',
'New customer') as customer_id_flag
Resident processed_xp_customer_master
Order by
customer_enrolled_on,
customer_id
;

I have ordered it by customers enrollment date and customer_id. 

Please suggest.

[marcus_sommer]

Try it with a change within the ordered fields - means at first to sort for the customer_id and then for the enrolled-values (the order in which the fields are listed in the order by statement is important).

- Marcus

[NitinK7]

Hi

try following it may be helpful to you

if(Peek('customer_id')<>customer_id,'New Customer','Existing Customer' ) as as customer_id_flag

Thanks,

Nitin.

[sebastiandperei · Accepted solution]

Hi! see attached qvw. the script:

processed_xp_customer_master:
LOAD * INLINE [
customer_id, customer_enrolled_on
1, 1/1/2020
2, 10/1/2020
3, 2/1/2020
4, 3/1/2020
2, 4/1/2020
5, 3/1/2020
3, 1/2/2020
];

customers:
NoConcatenate
load Distinct
customer_id as cust,
customer_enrolled_on as date,
if(Exists(cust,customer_id),1) as _exists
Resident processed_xp_customer_master
Order by customer_enrolled_on;

[sunainapawar]

Hi Marcus,

Thanks for the reply. I tried changing the order as well but its not working.

[sunainapawar]

Hello Sebastiandperei,

Thanks for the reply.

I tried to use the above script and loaded, but when i take the flag in front end and select 1 value to check,there are no customer_id's associated to it. I have attached the screenshot.

Thanks in advance.

[marcus_sommer]

Add a recno() and a rowno() to the load to see which record is ordered in which way. If it didn't worked like expected it could be caused through the fact that your fields aren't numeric or they might be also a dual() value and the numeric value is here different to the string-part.

- Marcus

[NitinK7 · Accepted solution]

Hi,

try following

customer_id, customer_enrolled_on
1, 1/1/2020
2, 10/1/2020
3, 2/1/2020
4, 3/1/2020
2, 4/1/2020
5, 3/1/2020
3, 1/2/2020
];

PQR:
LOAD *,
if(Peek('customer_id')<>customer_id,'New Customer','Existing Customer' ) as customer_id_flag
Resident ABC
Order By customer_id,customer_enrolled_on ;

DROP Table ABC;

[sunainapawar]

Hi Nitin,

Thanks for the reply. It is working.