Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
dirk_fischer
Contributor

Inner join on timestamps not working

Hello experts,

I'm struggeling with a piece of code,where I can't explain the behaviour and hope to get some help here. I do an interval match on the field OPP_END and then I want to join the fields IPP_NO and %ID_IPP into the table OPBP. It working with one type of files, where the timestamp is accurate to a second. If I use another type of files, where the timestamps are more detailled (down to miliseconds) it's not working anymore, although after the interval match, the table OPBP still has all the datasets. So for some reason the inner join is not working, although I take the fields to join on directly from the table IPP.

Does anybody see something wrong in my code or is there some experience saying, keep to timestamps as numbers to avoid this Kind of Trouble?

Already in advance, thank you very much for your help.

Best regards,

Dirk

Inner Join (OPBP) IntervalMatch( OPP_END, %MACHINE_ID )

Load Distinct

  IPP_START,

  IPP_END,

  %MACHINE_ID

Resident IPP

Where IPP_NO > 0;

Let vNoOfRows = NoOfRows( 'OPBP' );

TRACE 'After join IntervalMatch OPBP has ' & '$(vNoOfRows)' & ' rows.';

Inner Join (OPBP)

Load Distinct 

  IPP_START 

  ,IPP_END 

  ,%ID_IPP 

  ,IPP_NO

Resident IPP

Where Exists(IPP_START) And Exists(IPP_END);

Tags (2)
1 Solution

Accepted Solutions

Re: Inner join on timestamps not working

Decimal numbers (such as time stamps) are prone to rounding errors. I think Inner Join based on a decimal is probably not a great idea. Look here on an article about Rounding Errors

View solution in original post

3 Replies

Re: Inner join on timestamps not working

Decimal numbers (such as time stamps) are prone to rounding errors. I think Inner Join based on a decimal is probably not a great idea. Look here on an article about Rounding Errors

View solution in original post

dirk_fischer
Contributor

Re: Inner join on timestamps not working

Hi Sunny,

thank you very much for the hint. I converted the timestamp into an integer by using floor 0.0001 and multiplying the result by 10000. Now the interval match is working proper and the inner join also provides the expected results.

Regards,

Dirk

Re: Inner join on timestamps not working

Awesome