Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
i have a set of data( Ticket and date);
example data set:
ticket date
100 9/10/2015
100 10/10/2015
100 11/10/2015
101 10/10/2015
so i want to get the min date associated to every ticket ie:
ticket date
100 9/10/2015
101 10/10/2015
could you please help me get the same.
Regards
Mythili
try like this
LOAD ticket,
min(date) as Date
group by ticket
from table ;
try like this
LOAD ticket,
min(date) as Date
group by ticket
from table ;
in front-end normally below function is used -
firstsortedvalue(ticket,date)
In script it could be -
Load ticket,min(date) resident table group by ticket
Temp:
LOAD * Inline
[
ticket,date
100,9/10/2015
100,10/10/2015
100,11/10/2015
101,10/10/2015
];
LOAD ticket,
Date(min(date),'DD-MM-YYYY') as D
Resident
Temp
Group by
ticket;
Drop Table Temp;
Hi Mythili,
Try this
Ticket:
LOAD * Inline
[ticket, date
100, 9/10/2015
100, 10/10/2015
100, 11/10/2015
101, 10/10/2015];
Mindate:
LOAD ticket as TICKET, date(Min(date)) as MIN_DATE Resident Ticket group by ticket;
Drop table Ticket;
Regards
kalyan
In front-end like this,
Dimension - ticket
exp - min(date)
ensure date is already converted to date type using date and date# functions.
Thanks Avanish,but this is giving me the min date of the entire list and not according to the ticket.
Thanks... but this is giving me the min of the entire list and not according to the ticket.
did you tried this in script ?
Temp:
LOAD * Inline
[
ticket,date
100,9/10/2015
100,10/10/2015
100,11/10/2015
101,10/10/2015
];
LOAD ticket,
Date(min(date),'DD-MM-YYYY') as D
Resident
Temp
Group by
ticket;
Drop Table Temp;
if your trying the same in Front end try like this:
=aggr(min(date),ticket) this should work
Hi Mythili,
Please check the attached file and solution.
Data_Temp:
LOAD * Inline
[
ticket,date
100,9/10/2015
100,10/10/2015
100,11/10/2015
101,10/10/2015
];
Data:
LOAD ticket,
Date(min(date),'MM-DD-YYYY') as min_date
Resident Data_Temp
Group by
ticket;