Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
i have a very long list of dataset and below is a subset of it. Appreciate if you could advise the scripts over here as i am using personal edition.
Based on the question table, i would like to know the repeat customers trend in their place visited. for example, for the same customer A, based on their earliest date of visit, they went to Europe and subsequently went to China. with that i would like to have a table indicated below to sum up these trend of events by duplicate customer.
Please assist .
Question | ||
Customer | Place visited | Date of visit |
A | Europe | 10/4/2015 |
B | China | 14/9/2015 |
C | Singapore | 18/11/2015 |
A | China | 20/9/2015 |
B | Europe | 13/1/2015 |
C | Singapore | 12/1/2016 |
D | Malaysia | 13/2/2016 |
Answer | Visited place after their first visited place | |||
Repeated Customer visited place based on earliest date | Europe | China | Singapore | Total |
Europe | 1 | 1 | ||
China | 1 | 1 | ||
Singapore | 1 | 1 |
A:
LOAD * INLINE [
Customer, Place visited, Date of visit
A, Europe, 10/4/2015
B, China, 14/9/2015
C, Singapore, 18/11/2015
A, China, 20/9/2015
B, Europe, 13/1/2015
C, Singapore, 12/1/2016
D, Malaysia, 13/2/2016
]
;
temp1:
load *,
if (Customer=Previous(Customer),1,0) as customer_visit_seq,
if (Customer=Previous(Customer),Previous([Place visited]),'') as Last_Places
Resident A
order by Customer,[Date of visit];
drop table A;
in the chart, you can use the last_places and place visited as dimension and then sum(customer_visit_seq) as expression.
HTH
I think the answer should be below ?
Repeated Customer visited place based on earliest date | Europe | China | Singapore | Total |
Europe | 2 | 2 | ||
Singapore | 1 | 1 |
see the attached file.
yes you are right. As i am using personal edition, can you paste your scripts here?
A:
LOAD * INLINE [
Customer, Place visited, Date of visit
A, Europe, 10/4/2015
B, China, 14/9/2015
C, Singapore, 18/11/2015
A, China, 20/9/2015
B, Europe, 13/1/2015
C, Singapore, 12/1/2016
D, Malaysia, 13/2/2016
]
;
temp1:
load *,
if (Customer=Previous(Customer),1,0) as customer_visit_seq,
if (Customer=Previous(Customer),Previous([Place visited]),'') as Last_Places
Resident A
order by Customer,[Date of visit];
drop table A;
in the chart, you can use the last_places and place visited as dimension and then sum(customer_visit_seq) as expression.
HTH