topic Re: Compare rows and Create flag in App Development

Compare rows and Create flag

sunainapawar — Sat, 16 Nov 2024 18:52:07 GMT

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.

Re: Compare rows and Create flag

marcus_sommer — Mon, 30 Mar 2020 13:46:59 GMT

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

Re: Compare rows and Create flag

NitinK7 — Mon, 30 Mar 2020 14:06:39 GMT

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.

Re: Compare rows and Create flag

sebastiandperei — Mon, 30 Mar 2020 14:05:33 GMT

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;

Re: Compare rows and Create flag

sunainapawar — Tue, 31 Mar 2020 06:28:50 GMT

Hi Marcus,

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

Re: Compare rows and Create flag

sunainapawar — Tue, 31 Mar 2020 06:33:31 GMT

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.

Re: Compare rows and Create flag

marcus_sommer — Tue, 31 Mar 2020 06:38:11 GMT

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

Re: Compare rows and Create flag

NitinK7 — Tue, 31 Mar 2020 07:40:02 GMT

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;

Re: Compare rows and Create flag

sunainapawar — Tue, 31 Mar 2020 10:45:54 GMT

Hi Nitin,

Thanks for the reply. It is working.