Show Changes by minimum date

[sjhussain]

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.

[Chanty4u]

try

=FirstSortedValue(Date,Staus)

or

=FirstSortedValue(Staus,Date)

[MK_QSL]

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;

[sjhussain]

Dear Chanty,

Thanks for the prompt response.

How would I use firstSortedValue in the load script?

[#5]

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;

[sjhussain]

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

[MK_QSL]

Here status is changing more than once per Name so using a Group By with Name and Status is not a good idea.

[sjhussain]

Dear Manish,

I have about 20 Million+ records.  I will try and see how long it takes with this solution.

Thanks.

[MK_QSL]

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.