Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Compare rows and Create flag

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.

 

2 Solutions

Accepted Solutions
Specialist
Specialist

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;

View solution in original post

Creator III
Creator III

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;

flag.PNG

 

View solution in original post

8 Replies
MVP & Luminary
MVP & Luminary

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

Creator III
Creator III

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.

Specialist
Specialist

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;

View solution in original post

Contributor III
Contributor III

Hi Marcus,

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

Contributor III
Contributor III

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.

 

MVP & Luminary
MVP & Luminary

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

Creator III
Creator III

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;

flag.PNG

 

View solution in original post

Contributor III
Contributor III

Hi Nitin,

Thanks for the reply. It is working.