Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Based on his first initial 'visit date' and with reference to his 'Place', identify the count of the 2nd place

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 PLACEVISIT DATE
PETERA 13-09-15
PETERB 10-10-15
PETERC29-09-15
TOMD18-06-15
TOMA 27-08-15
ALANB25-06-15

ANSWER below  

2nd Visit date
Initial Visit dateABCD
A 11
B
C
D1
1 Solution

Accepted Solutions
sunny_talwar

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;

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

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;

Capture.PNG