Data Filter

[brindlogcool]

Hi,

I have an table with thousands of rows with audit details in it. It will have the original record and modified record in the same table.I need to consider only the modified records.The first and third records are same but i need to consider only the thrid record.

  Id   user             Date                       Teamcode       Reason        Audit date

    1    01           04/02/2012                      A1            Planned        04/02/2012 

   2    02           04/04/2012                      A2             Active          04/04/2012   

   3    01           04/02/2012                      A2             Active          04/08/2012      

I need the output as below

Id   user             Date                       Teamcode       Reason        Audit date

   2    02           04/04/2012                      A2             Active          04/04/2012   

   3    01           04/02/2012                      A2             Active          04/08/2012      

[hic]

You can sort them according to Audit date, and just load the latest:

RawData:

Load

          Id as TempID,

          user,

          Date,

          Teamcode,

          Reason,

          [Audit date] From File;

AuditData:

Load * where not Exists(ID);

Load

          TempID as ID,

          user,

          Date,

          Teamcode,

          Reason,

          [Audit date]

          resident RawData

          order by [Audit date] desc          ;

Drop Table RawData;

[brindlogcool]

Hi Enric,

Thanks for the response.

As per your code you are just sorting it. Where you are filtering it .

Could you please explain your code logic.

[hic]

The "Load * where not Exists(ID)" is a filter that loads only those IDs that have not been loaded before.

HIC

[Anonymous]

Planned_Table:

Load

          TempID           as ID,

          user                     as Planned_User,

          Date                     as Planned_Date,

          Teamcode           as Planned_Team_Code,

          1                     as Planned_Flag,

          [Audit date]           as Planned_Date

From File

Where Reason = 'Planned';

Join (Planned_Table)

Load

          TempID           as ID,

          user                     as Active_User,

          Date                     as Active_Date,

          Teamcode           as Active_Team_Code,

          1                     as Active_Flag,

          [Audit date]           as Active_Date

From File

Where Reason = 'Active';

/// The above create a table with a 'Planned Record' and any corresponding 'Active' record on the same row for a user id.

This can then be used for table queries, set analysis etc.