Max function help

[Anonymous]

Hi All,

  I was trying to take the max(Notified_date)'s Ticket #.How ever I am not able to get the ticket details.

My Data looks below

Notified_date,                   TKT_Primary_Key,               Ticket#

04/10/2016 08:15:25 AM,    8841SRO789,                888812345WW

04/06/2016 07:13:25 PM,     8841SRO789,                 9999256BB

My syn goes like below

Load Max(Notified_date) as Notified_date,

        TKT_Primary_Key,

        Ticket #

group by TKT_Primary_Key,Ticket #

Resident Temp Table;

So here I am getting max of date but the ticket details not coming. I hve tried with firstsortedvalue as well.

All I need is

04/10/2016 08:15:25 AM,

8841SRO789,

888812345WW

-Jay

[sunny_talwar]

Try this:

LOAD Max(Notified_date) as Notified_date,

           TKT_Primary_Key,

           FirstSortedValue([Ticket #], -Notified_date) as [Ticket #]

Resident [Temp Table]

Group By TKT_Primary_Key;

[swuehl]

What do you mean with 'my ticket details not coming'?

If you

group by TKT_Primary_Key,Ticket #

you should see both lines from your sample (though that's probably not what you want, I think you want to group only by the primary key, as Sunny suggested).

[sunny_talwar]

Try this based on your sample:

Table:

LOAD * Inline [

Notified_date,                  TKT_Primary_Key,              Ticket#

04/10/2016 08:15:25 AM,    8841SRO789,                888812345WW

04/06/2016 07:13:25 PM,    8841SRO789,                9999256BB

];

FinalTable:

NoConcatenate

LOAD Max(Notified_date) as Notified_date,

  TKT_Primary_Key,

  FirstSortedValue([Ticket#], -Notified_date) as [Ticket#]

Resident Table

Group By TKT_Primary_Key;

DROP Table Table;