topic Re: Loosing data with left join in QlikView

Loosing data with left join

zet — Sat, 16 Nov 2024 17:40:22 GMT

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 ?

Re: Loosing data with left join

edwin — Wed, 23 Dec 2020 17:45:29 GMT

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

Re: Loosing data with left join

dplr-rn — Wed, 23 Dec 2020 19:07:12 GMT

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

Re: Loosing data with left join

zet — Wed, 23 Dec 2020 19:52:21 GMT

Re: Loosing data with left join

zet — Wed, 23 Dec 2020 19:53:24 GMT

Re: Loosing data with left join

zet — Wed, 23 Dec 2020 19:55:54 GMT

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

Re: Loosing data with left join

dplr-rn — Wed, 23 Dec 2020 20:18:46 GMT

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

Re: Loosing data with left join

dplr-rn — Wed, 23 Dec 2020 20:35:56 GMT

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,

Re: Loosing data with left join

zet — Wed, 23 Dec 2020 21:13:21 GMT

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

Re: Loosing data with left join

zet — Wed, 23 Dec 2020 21:21:45 GMT

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 ?