Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
pid | date | New |
7344 | 01/02/2016 | new |
7344 | 01/03/2016 | rescheduled |
7344 | 04/03/2016 | rescheduled |
4555 | 04/02/2016 | new |
4555 | 06/03/2016 | rescheduled |
Regards,
Anand
In My Case It took more time load
Is there any alternative way to done this?
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.
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
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;