- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Show Changes by minimum date
Hello,
Hope everyone is fine.
We have a scenario in which we would like to show the changes by minimum dates.
For example we have the data as follows:
Date, Name, Status
10/31/2016, John, Active
11/01/2016, John, Active
11/02/2016, John, InActive
11/03/2016, John, InActive
11/04/2016, John, Active
11/05/2016, John, Active
11/06/2016, John, Active
11/07/2016, John, InActive
11/08/2016, John, Active
Now the records which we would like to show are as follows:
10/31/2016, John, Active
11/02/2016, John, InActive
11/04/2016, John, Active
11/07/2016, John, InActive
11/08/2016, John, Active
So we are taking the minimum date of each change. Will appreciate if someone could assist.
Thanks.
- Tags:
- min date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try
=FirstSortedValue(Date,Staus)
or
=FirstSortedValue(Staus,Date)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
SET DateFormat='MM/DD/YYYY';
Temp:
Load * Inline
[
Date, Name, Status
10/31/2016, John, Active
11/01/2016, John, Active
11/02/2016, John, InActive
11/03/2016, John, InActive
11/04/2016, John, Active
11/05/2016, John, Active
11/06/2016, John, Active
11/07/2016, John, InActive
11/08/2016, John, Active
];
Left Join (Temp)
Load
Date,
Name,
Status,
If(Name <> Previous(Name), 1, If(Status <> Previous(Status), 1,0)) as Flag
Resident Temp
Order By Date, Name;
Now you can use Flag = 1 which will give you required fields..
If you want to remove all lines except flag 1 use below also.
NoConcatenate
Final:
Load * Resident Temp Where Flag = 1;
Drop Table Temp;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear Chanty,
Thanks for the prompt response.
How would I use firstSortedValue in the load script?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
t1:
LOAD *,RowNo() as Key INLINE [
Date, Name, Status
10/31/2016, John, Active
11/01/2016, John, Active
11/02/2016, John, InActive
11/03/2016, John, InActive
11/04/2016, John, Active
11/05/2016, John, Active
11/06/2016, John, Active
11/07/2016, John, InActive
11/08/2016, John, Active
];
Left Join
LOAD Key+1 as Key,Status as Status1 Resident t1;
NoConcatenate
t:
LOAD * Resident t1 Where Status<>Status1;
DROP Table t1;
DROP Fields Key,Status1;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Manish,
Thanks for the prompt response.
I will try your solution but wanted to know whether it is possible to use group in this case? I tried using it but the problem is that i am not able to get the changes. I only get one minimum value per change.
Sincerely,
Syed Jawwad Hussain
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here status is changing more than once per Name so using a Group By with Name and Status is not a good idea.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear Manish,
I have about 20 Million+ records. I will try and see how long it takes with this solution.
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Creating a flag with such big records will always be tough wrt to performance.. But even though if you use Group by (Don't know how in this scenario), it will also little performance killing. But Front end you will get better response as flag creation is in back end.