Cells merge problem with Excel

[#1]

Hi,

I have an excel table that looks like this

id	statut	date_start	date_end	duree
id1	demande ouverte	01/05/2010 10:02		-
id1	demande ouverte		02/06/2010 11:13	-
id2	qualif	02/05/2010		-
id2	qualif		03/05/2010 12:25	-

and I want to merge cells to obtain duree (duree= date_end-date_start)

We have 2 rainures with code

main 1:

StartDate:

LOAD

[Artifact ID]

,

'Qualification DSI'

as status

,

SubmissionDate

as

changedate_start

RESIDENT

Artefacts

WHERE

NOT IsNull(SubmissionDate)

;

// identification de la date de cloture de l'artefact.

ClosedDate:

LOAD

[Artifact ID]

,

'Demande Fermée'

as status

,

ClosedDate

as

changedate_end

RESIDENT

Artefacts

WHERE

NOT IsNull(ClosedDate)

main 2

HS_1:

LOAD

id as [Artifact ID],

old_value

as ov,

new_value

as status,

timestamp

(change_date) as changedate_start

FROM

(

biff, embedded labels, table is Feuille1$)

WHERE

(field = 'status');

HS_2:

LOAD

id as [Artifact ID],

old_value

as status,

new_value

as nv,

timestamp

(change_date) as changedate_end

FROM

(

biff, embedded labels, table is Feuille1$)

WHERE

(field = 'status');

OUTER

JOIN (HS_1)

LOAD

* RESIDENT HS_2;

HS_f:

LOAD

[Artifact ID]

,

status

,

changedate_start

,

changedate_end

,

Time

(changedate_end-changedate_start) as duration

RESIDENT

HS_1;

drop

table

HS_1;

drop

table

HS_2;

Yours faithfully

Regards

[martin59]

Hi,

it seems your script have several issues.

To calculate an interval between two dates or timestamp, you have to use the timestamp() function.

But there was an issue due to a comma juste before.

Try this script :

StartDate:
LOAD
 [Artifact ID],
 'Qualification DSI' as status,
 SubmissionDate as changedate_start
RESIDENT Artefacts
WHERE NOT IsNull(SubmissionDate) ;

// identification de la date de cloture de l'artefact.
ClosedDate:
LOAD
 [Artifact ID],
 'Demande Fermée' as status,
 ClosedDate as changedate_end
RESIDENT Artefacts
WHERE NOT IsNull(ClosedDate) ;
//main 2

HS_1:
LOAD
 id as [Artifact ID],
 old_value as ov,
 new_value as status,
 timestamp(change_date) as changedate_start
FROM 
(biff, embedded labels, table is Feuille1$)
WHERE (field = 'status');

HS_2:
LOAD
 id as [Artifact ID],
 old_value as status,
 new_value as nv,
 timestamp(change_date) as changedate_end
FROM

(biff, embedded labels, table is Feuille1$)
WHERE (field = 'status');

OUTER JOIN (HS_1)
LOAD * RESIDENT HS_2;

HS_f:
LOAD
 [Artifact ID],
 status,
 changedate_start,
 changedate_end,
// ,
 Interval(changedate_end-changedate_start) as duration
RESIDENT HS_1;

drop table HS_1;
drop table HS_2;

[#3]

Hi,

Thanks for your answer but I have always the same problem because I can't merge cells

In fact, I have several cells which have the same id

id	statut	date_start	date_end	duree
id1	demande ouverte	01/05/2010 10:02		-
id1	demande ouverte		02/06/2010 11:13	-
	dsi		03/05/2010 14:12	-
id1	qualification	02/05/2010 13:20		-
id2	qualif	02/05/2010		-
id2	qualif	01/05/2010 12:00	03/05/2010 12:25	25

I think I must merge cells then I use interval function??

Yours faithfully

[martin59]

Hi,

Could you send to me a part of your application for testing ?