Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
zet
Contributor III
Contributor III

Loosing data with left join

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 :

mzitouni_0-1608738845658.png

and this is my second table filled with quantity:

mzitouni_1-1608738960317.png

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

mzitouni_2-1608739055882.png

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 ?

 

 

1 Solution

Accepted Solutions
dilipranjith
Partner
Partner

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

 

 

View solution in original post

9 Replies
edwin
Specialist III
Specialist III

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

dilipranjith
Partner
Partner

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
Contributor III
Contributor III
Author

 
zet
Contributor III
Contributor III
Author

 
zet
Contributor III
Contributor III
Author

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

dilipranjith
Partner
Partner

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

 

 

View solution in original post

dilipranjith
Partner
Partner

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
Contributor III
Contributor III
Author

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

zet
Contributor III
Contributor III
Author

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 ?