Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I want to create a new field in Load clause which is named [Last day]. And this is calculated by the two existed field in the same load clause [submit date] & [Last modified date].
I think i find another problem, can qlikview make minus operation just on the date? If below two types date is captured with left() function i think the minus operation cannot be done. So how can i calculate the duration between submit and last modified?
So I programmed the script like this:
LOAD
[Last Modified Date],
[Submit Date],
([Last Modified Date] - [Submit Date]) as
[Last day]
from
xxxxxx
Could you please help me on this issue?
Many thanks and thanks in advance!
br,
lisen
Interval([Last Modified Date] - [Submit Date]) as [Last day]
hi
use like
Num[Last Modified Date] - [Submit Date]) as [Last day]
Hi,
You can try like this
LOAD
*,
Interval([Last Modified Date] - [Submit Date], 'DD') as [Last day]
FROM DataSource;
This will return the difference in Days.
Regards,
Jagan.
Hi Jagan,
here is another issue.
the Last modified date and Submit date is combined with Date and time. SO I use Left first to capture the date. and then use Date# to change the char type to date type. then I use Interval to make the minus.
BUT the system tells me that the field(<Submit>) cannot be found... could you please help me again?
Many thanks and thanks in advance~
code here:
LOAD
[Last Modified Date],
[Submit Date],
DATE#(left([Submit Date],10),'M/D/YYYY') as Submit,
DATE#(left([Last Modified Date],10),'M/D/YYYY') as Modified,
Interval(Submit - Modified,DD) as [Last day]
FROM
(
LOAD *,
Interval(Submit - Modified,DD) as [Last day];
LOAD
[Last Modified Date],
[Submit Date],
DATE#(left([Submit Date],10),'M/D/YYYY') as Submit,
DATE#(left([Last Modified Date],10),'M/D/YYYY') as Modified
FROM
(
Hi Lisen,
The following script will create a separate column in the table as LastDay with the data in the difference in dates in days.
num([Last Modified Date]) - num([Submit Date]) AS LastDay
Hope this helps.
Regards,
Snehal Nabar
Hi,
Try like this
Data:
LOAD
*,
Interval(Submit - Modified,'DD') as [Last day];
LOAD
[Last Modified Date],
[Submit Date],
DATE#(left([Submit Date],10),'M/D/YYYY') as Submit,
DATE#(left([Last Modified Date],10),'M/D/YYYY') as Modified
FROM
(
Regards,
Jagan.