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: 
scotly-victor
Creator II
Creator II

How to find new or resheduled

How Can I find a person appointment new or rescheduled ?

pid      date

7344  01/02/2016

7344  01/03/2016

7344  04/03/2016

4555  04/02/2016

4555   06/03/2016

Desired Output is

pid      type               date

7344  new               01/02/2016

7344  rescheduled   01/03/2016

7344  rescheduled   04/03/2016

4555   new              04/02/2016

4555   reshdeduled  06/03/2016

5 Replies
its_anandrjs

Try this in the load script

Data:
LOAD * Inline
[
pid,      date
7344,  01/02/2016
7344,  01/03/2016
7344,  04/03/2016
4555,  04/02/2016
4555,  06/03/2016
]
;

NoConcatenate
LOAD
*,
If(pid = Previous(pid),'rescheduled','new') as New
Resident Data;
DROP Table Data;

Output you Get

   

piddateNew
734401/02/2016new
734401/03/2016rescheduled
734404/03/2016rescheduled
455504/02/2016new
455506/03/2016rescheduled

Regards,

Anand

scotly-victor
Creator II
Creator II
Author

In My Case It took more time load

Is there any alternative way to done this?

terezagr
Partner - Creator III
Partner - Creator III

Try the following:

Table1:

LOAD * Inline
[
pid,      date
7344,  01/02/2016
7344,  01/03/2016
7344,  04/03/2016
4555,  04/02/2016
4555,  06/03/2016
]
;

NewTable:

Load*,

if(pid = peek('pid',-1),'rescheduled','new') as appointment_type

Resident Table1;


Drop table Table1;


It should not take too much longer to load. It would, if you would not do the Drop table statement as it would start to create synthetic key. But as you are dropping the Table1 and keeping only the NewTable, this should be fairly quick load.



its_anandrjs

Better if you create this in script part as i explain to you, what ever it is taken time but it is better to maintain and show on presentation layer as well as.

Regards

Anand

Anonymous
Not applicable

I prefer to use mapping for everything

Data:

LOAD * Inline

[

pid, date

7344, 01/02/2016

7344, 01/03/2016

7344, 04/03/2016

4555, 04/02/2016

4555, 06/03/2016

];

DateMap:

MAPPING LOAD DISTINCT

  pid as Input,

  date(min(date)) as Output

RESIDENT Data

GROUP BY pid;

LEFT JOIN (Data) LOAD

  pid,

  date,

  if(applymap('DateMap',pid)=date, 'new', 'rescheduled') as type

RESIDENT Data;