Finding the actual temperature as well as the temperature 12h ago

[Anonymous]

Dear all,

I am currently working on a topic and can't find the solution:

Description of data:

I have two datasets:

1. List of accidents (incl. Date of accident, No, type of car, Allignment to the Weather Station (ID) etc.)

2. Huge set of Weather data (incl. ID, Date, temperature, amount of precipitation)

So, I set the load script having the weather station of both tables as ID

Here is my question:

I want to design a table, having:

- the number

- the date

- the temperature

- the temperature 12 hours ago.

I was trying to use the Above() function, but that didn't work.

Any other ideas?

Thank you very much and I hope I described it clearly.

Thorsten

[rubenmarin · Accepted solution]

Hi Thorsten, the time has also minutes and seconds or it's all only the hours?.

In example, if you have the temperature at 15:03:01.2435, find a record exactly 12 hours later can be tricky, maybe it's in another second.

It the records are all just hours (like 01/12/2016 15:00:00.0000) maybe it will be easier to have this precalculated in script. If you have a table with: ID, Date, temperature and precipitation, from that table you can create the records with:

Left Join (YourTable)

LOAD ID, // this should be an unique key

     Date(Date+0.5) as Date,

     temperature as temperature12HoursAgo

resident YourTable;

[Anonymous]

Hi Ruben,

thank you for the quick answer!

Actually I think I'm not sure about the answer.

Attached I put the example QVD-File and the data sources (probably that makes it easier)

Can you please have a look and help me?

Kind regards

Thorsten

[rubenmarin]

Hi Thorsten, attached qvw with some modifications:

I removed the date from the 2nd table (that will join with only the 3 dates informed, I think this tabla must be joined with all the recods based only on ID, not with date.

Then I added what I commented in my last post.

[Anonymous]

Dear Ruben,

THANK YOU VERY MUCH!

Great!