Add date and datas with "while" during load

[smic]

Hello,

Here is the problem that I cannot solve ... I need to load data from an excel file that contains conversion rates for currencies. Here is what I have as input:

Currency	Date_END	Rates
AUD	31/10/2018	1.4068
AUD	30/11/2018	1.3799
AUD	31/12/2018	1.3893
AUD	31/01/2019	1.4005

Here is what I would like to get:

Currency	Date_END	Rates
AUD	31/10/2018	1.4068
AUD	01/11/2018	1.3799
AUD	02/11/2018	1.3799
AUD	03/11/2018	1.3799
AUD	04/11/2018	1.3799
AUD	05/11/2018	1.3799
...	...	...
AUD	30/11/2018	1.3799
AUD	01/12/2018	1.3893
AUD	02/12/2018	1.3893
AUD	03/12/2018	1.3893
...	...	...
AUD	31/12/2018	1.3893

I must "create" the lines for the missing days, keeping the rates entered in the line containing the "date_end". So each time I have a time interval to enter, keeping the data from the "max" line of this interval ...

I carried out several tests with "while" but did not arrive at the good result ... If someone can help me please?

Regards

[jyothish8807]

Hi Smic,

May be the below approach help:

Create a month year filed in you current table and create a separate table (or master calendar) with  Month year and date and connect or left join the month Year field:

Load

Date(Date_END,'MM YYYY') as MonthYear,

*

from <>;

leftjoin

Load

MonthYear, Date (All the dates in the month)

from <Master calander>

[smic]

Thanks you for reply,

I managed to fill in the missing dates. But how can I browse my tables and feed the "Rates" field if the date field is lower than the date field of another table please?

Regards

[jyothish8807 · Accepted solution]

Hi Smic,

Since you have the same rates for the entire month it should not be an issue. The moment  you do a left join all the rates will be replicated to each dates from your second table.

May be if you can share what have you build may help me in guiding you in a better way 🙂

Br,

KC