topic Re. :Cells merge problem with Excel in QlikView

Cells merge problem with Excel

Mon, 09 Aug 2010 13:03:56 GMT

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

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

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

Re. :Cells merge problem with Excel

martin59 — Mon, 09 Aug 2010 15:29:26 GMT

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;

Re. :Cells merge problem with Excel

Tue, 10 Aug 2010 09:19:02 GMT

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

Re. :Re: Re. :Cells merge problem with Excel

martin59 — Thu, 12 Aug 2010 09:14:37 GMT

Hi,

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