rows merge troubles

[#1]

Hi all,

I'm new on this forum, but i read since few weeks its content for my currents developments and i would like to thank you all for yours contributions that are really helpful.

But now i facing a problem and i don't find any answer, perhaps you could help me to build a solution.

1/ Step 1 :

At the begining i have an excel file containing the change history of values; so you have in line :

(table 1) : ID | "name of the value changed" | change date | old value | new value

I just want to load so lines of this table; lines that concern the "status", so i've load the file with clause WHERE "name of the value changed" = 'status'

==> that works and it's ok.

2/ Step 2 :

After that i have to rotate the table to obtain :

ID | old value OR new value | change date start | change date end

The aim is to be able to calculate durations of each 'status' given by "old value OR new value".

==> that works and it's ok.

BUT, my problem is that 2 dates are missing.

> The first missing date is the creation date that will allow me to calcuate duration between the creation and the first status beginning.

> The second missing date is the closed date, that will allow me to calculate the last duration (last status of the life cycle)

These 2 dates are existing in another file that present current state of the object. In this file we have the creation date and the closed date.

So i have to load them.

3/ Step 3 :

I load these 2 dates : ID | Creation Date | Closed date into a second table.

To make the JOIN possible, i split the table into 2 temporary tables :

(table 2) ID | 'my initial status' | Creation Date ==> 'my initial status' contains the exact label of the status as existing into the Step 2

(table 3) ID | 'my final status' | Closed Date ==> 'my final status' contains the exact label of the status as existing into the Step 2

4/ Step 4 :

Then i try to merge the contents of the 3 tables :

(table 1) JOIN (table 2) JOIN (table 3)

BUT the result is not what i was expecting.

Result obtained :

ID | Status | start date | end date

1 | A | xx:xx:xx |

1 | A | | xx:xx:xx

1 | B | xx:xx:xx |

1 | B | | xx:xx:xx

1 | C | xx:xx:xx | xx:xx:xx

1 | D | xx:xx:xx | xx:xx:xx

1 | E | xx:xx:xx | xx:xx:xx

whereas i was expecting :

ID | Status | start date | end date

1 | A | xx:xx:xx | xx:xx:xx

1 | B | xx:xx:xx | xx:xx:xx

1 | C | xx:xx:xx | xx:xx:xx

1 | D | xx:xx:xx | xx:xx:xx

1 | E | xx:xx:xx | xx:xx:xx

It seems that the JOIN doesn't work with the status when i have on one side status writen into the table and on the other side the status written by end into a LOAD.

[#2]

Do you can to upload the qvw?

[#3]

here is the part of the code :

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

ClosedDate:
LOAD
 [Artifact ID],
 'Action End' as status,
 ClosedDate as changedate_end
RESIDENT Artefacts
WHERE NOT IsNull(ClosedDate);

HS_1:
LOAD id as [Artifact ID],
 old_value as ov,
 new_value as status,
 timestamp(change_date) as changedate_start
FROM
DataSources\history.demandes_0.xls
(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
DataSources\history.demandes_0.xls
(biff, embedded labels, table is Feuille1$)
WHERE(field = 'status');

OUTER JOIN (HS_1)
LOAD * RESIDENT HS_2;

HistoStatus:
LOAD
 [Artifact ID],
 status,
 changedate_start,
 changedate_end
RESIDENT HS_1;

drop table HS_1;
drop table HS_2;

OUTER JOIN (HistoStatus)
LOAD * RESIDENT StartDate;

OUTER JOIN (HistoStatus)
LOAD * RESIDENT ClosedDate;

drop table StartDate;
drop table ClosedDate;

Hope this will help you to understand my problem.

Thanks for your help.

[#4]

I need a data for testing this example.

But i think that your join must have only a key ( [Artifact ID] ) and not ( [Artifact ID] and Status).

OUTER JOIN (HS_1)
LOAD * RESIDENT HS_2;

[#5]

This JOIN (you mentioned) works fine.

The problem occurs on the next joins with tables StartDate and CloseDate.

The JOIN works with all the columns having the same name. I think the poblem comes from the value set manually in these tables. Obviously, QV doesn't identify the value manually set

'Action Start' as status,

set into the StartDate table as identical to the values contained in the column status of the tableHistoStatus.

[#6]

no one have an idea concerning the source of the problem ?

I really don't understand why the second and third join doesn't merge lignes containing the same 'status'. On one side, the value in the column used to merge, comes from the Excel file, on the other hand, the value was created manually in the declaration of the table (eg. StartTable and CloseTable) and when i do the Join , QV return 2 lignes whereas it should return one.

I don't have any solution and any idea of how to solve or waorkaround this problem and i need to have the values on a sigle ligne to allow me to calculate interval between the dates.

Thanks for your help.

[#7 · Accepted solution]

Solution or at least workaround found.

The problem was not due to manually definition of the status value but to the coluns containing the date into the tables StartDate and CloseDate.

Indeed, the columns were named with the same name than the column in HistoStatus table; due to that QV try to JOIN these columns too and as the values for ID and status columns were the same but not the date value, QV can merge the lines and so create a new one.

The solution was to rename the columns into the temporary tables (startdate and closedate) and then using a condition fonction to replace null values by the right one.

Bye.