Loosing data with left join

[zet]

Hello,

I have two qvd files (in attachement) that I want to merge to only one table with left join

this is my first table where filled with hours :

and this is my second table filled with quantity:

When I do the left join I don't understand why I miss those 2 quantities :

This is my script :

table1:
NoConcatenate
load * from $(vQVD)\Horaires.qvd(qvd);
left join(table1)
Load * from $(vQVD)\Archivage.Qvd(Qvd);

Could you please help ?

[edwin]

is it possible to attach the QVDs?  
if these are all the columns of the QVDs, then the script should work as intended.  there may be something with the data in the QVD that is causing this so attaching the QVD would help shed light on what the issue is

[dplr-rn]

I would hazard a guess that it maybe something with time. there maybe some difference in seconds.

load both qvds in a separate app and Format to show seconds and compare

[zet]

Those are the QVD files I didn't know how to do to show seconds

[dplr-rn · Accepted solution]

Decimal numbers (such as time stamps) are prone to rounding errors. because of that joining with a decimal is probably not a great idea. Look here on an article about Rounding Errors

i suggest splitting the column into time and date and then join. e.g below

Test:
LOAD
    "Minute",
    Jour,
utc_obs,
    SubField(timestamp(utc_obs,'YYYY-MM-DD hh:mm'),' ',1) as date_field,
    time(SubField(timestamp(utc_obs,'YYYY-MM-DD hh:mm'),' ',2),'hh:mm') as time_field

    FROM [lib://Downloads/Community/Horaires.qvd]
(qvd);
left join (Test)
LOAD
    //utc_obs,
    SubField(timestamp(utc_obs,'YYYY-MM-DD hh:mm'),' ',1) as date_field,
    time(SubField(timestamp(utc_obs,'YYYY-MM-DD hh:mm'),' ',2),'hh:mm') as time_field,

    quantity
FROM [lib://Downloads/Community/Archivage.qvd]
(qvd);

//drop new columns

[dplr-rn]

please note i didnt use the xisting columns Minute and Jour because they are full timestamps as well not date and time separated. (you can check by doing num(Jour))

using new fields i forced and separated date and time fields.

Actually to be on safer side i would even multiply  time field and make it into an integer and then use that for joiinging. 

e.g.

    floor(Num(SubField(timestamp(utc_obs,'YYYY-MM-DD hh:mm'),' ',2))*10000) as time_field,

[zet]

Thanks for the solution and also for the article it was really helpful !!

[zet]

I added this function but I want to show time_field in time format when loading final results. Do you know which function should I use to  make it from integer to time ?