Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two fields 'Created_Time' and 'Resolved_time'.
1- I want to add a calculated field as "Time". my expression was : [Resolved_time - Created_Time] but the result is zero, I'm not sure why although the two columns are in date format in data source excel sheet.
2- I want also to add a column " order Status" depending in the Time results above. if the time calculated then the status "closed". if it's null then the status "Open"
Created_Time | Resolved_time | Time in days and hours | status |
23/07/2018 19:30 | 24/07/2018 10:30 | ||
02/09/2018 13:11 | - | ||
01/11/2018 12:45 | 01/11/2018 3:45 |
Add in timestamp formatting in your load script, possibly QV reading the timestamp as string:
LOAD *,
Resolved_time-Created_Time as [Time in Days & Hours],
if(isnull(Resolved_time)or len(Resolved_time)<=1,'Open','Closed') as Status;
load
timestamp#(Created_Time,'DD/MM/YYYY hh:mm') as Created_Time,
timestamp#(Resolved_time,'DD/MM/YYYY hh:mm') as Resolved_time
inline [
Created_Time,Resolved_time
23/07/2018 19:30,24/07/2018 10:30
02/09/2018 13:11,-
01/11/2018 12:45,01/11/2018 3:45
];
Thanks and regards,
Arthur Fong
Add in timestamp formatting in your load script, possibly QV reading the timestamp as string:
LOAD *,
Resolved_time-Created_Time as [Time in Days & Hours],
if(isnull(Resolved_time)or len(Resolved_time)<=1,'Open','Closed') as Status;
load
timestamp#(Created_Time,'DD/MM/YYYY hh:mm') as Created_Time,
timestamp#(Resolved_time,'DD/MM/YYYY hh:mm') as Resolved_time
inline [
Created_Time,Resolved_time
23/07/2018 19:30,24/07/2018 10:30
02/09/2018 13:11,-
01/11/2018 12:45,01/11/2018 3:45
];
Thanks and regards,
Arthur Fong
Did Arthur's post help you get what you needed, or are you still working upon things? If the prior, be sure to use the Accept as Solution button on the post that helped you to give credit to the poster and let others know that was the correct solution. If you are still working on things, let us know where they stand, so we can try to come up with some other ideas.
Here is a Design Blog link that may be helpful as well:
https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849
https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157
Regards,
Brett