Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI ALl,
I have a long list and table 1 show the subset. I would like to form a table to:
1. know how many people based on their earliest date visited which 'place' and subsequently went to other place/same place in his 2nd visit
2. for single event, it will not be counted.
pls assist.
Table 1
Name | PLACE | VISIT DATE |
PETER | A | 13-09-15 |
PETER | B | 10-10-15 |
PETER | C | 29-09-15 |
TOM | D | 18-06-15 |
TOM | A | 27-08-15 |
ALAN | B | 25-06-15 |
ANSWER below
2nd Visit date | ||||
Initial Visit date | A | B | C | D |
A | 1 | 1 | ||
B | ||||
C | ||||
D | 1 |
May be like this:
Table:
LOAD Name,
PLACE,
[VISIT DATE]
FROM
[https://community.qlik.com/thread/215039]
(html, codepage is 1252, embedded labels, table is @1);
TempTable:
LOAD *,
If(Name = Previous(Name), Peek('NEW_PLACE'), PLACE) as NEW_PLACE,
IF(Name = Previous(Name), 1, 0) as Flag
Resident Table
Order By Name, [VISIT DATE];
FinalTable:
LOAD Name,
PLACE,
[VISIT DATE],
If(Flag = 1, NEW_PLACE) as NEW_PLACE
Resident TempTable;
DROP Tables Table, TempTable;
May be like this:
Table:
LOAD Name,
PLACE,
[VISIT DATE]
FROM
[https://community.qlik.com/thread/215039]
(html, codepage is 1252, embedded labels, table is @1);
TempTable:
LOAD *,
If(Name = Previous(Name), Peek('NEW_PLACE'), PLACE) as NEW_PLACE,
IF(Name = Previous(Name), 1, 0) as Flag
Resident Table
Order By Name, [VISIT DATE];
FinalTable:
LOAD Name,
PLACE,
[VISIT DATE],
If(Flag = 1, NEW_PLACE) as NEW_PLACE
Resident TempTable;
DROP Tables Table, TempTable;