Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table which have columns like ID, Name , Date .Date is in dd/mm/yyy hh:mm:ss.
For one id there are multile rows because of different date timestamp. Example
ID Name Date
1 AA 24/10/2019 13:10:45
1 AA 24/10/2019 14:20:11
i want and ouptut to be like below , a single row with no timestamp just date
ID Name Date
1 AA 24/10/2019
Thanks
Hello,
You could try this:
Temp:
load * inline
[
ID, Name, Date
1, AA, 24/10/2019 13:10:45
1, AA, 24/10/2019 14:20:11
];
Data:
Load distinct
ID,
Name,
Date#(left(Date,10),'DD/MM/YYYY') as Date_corrected
resident Temp;
drop table Temp;
Table Temp contains your data, table 'Data' ends up with only one row because we load the distinct combinations of ID, Name and Date (without timestamp). Is this what you want to do?
Greetings,
Tim Wensink
While loading the data you can truncate the time (fraction) portion from your date field like:
Load
Date(Floor(DateField)) as NewDate
Thanks Tim, Its giving me date in number format.