create new field in load clause with minus

[#1]

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

[MarcoWedel]

Interval([Last Modified Date] - [Submit Date]) as [Last day]

[Anonymous]

hi

use like

Num[Last Modified Date] - [Submit Date]) as [Last day]

[jagan]

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.

[#5]

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

(biff, embedded labels, table is [Sheet 1$]);

[MarcoWedel]

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

(biff, embedded labels, table is [Sheet 1$]);

[#7]

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

[jagan]

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

(biff, embedded labels, table is [Sheet 1$]);

Regards,

Jagan.