Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Contributor II

## Extract all year dates between two dates in a table

Hi Community,

I have the following data:

 Article ID Price Price is valid from Price is valid to 1 3 01.01.2019 01.06.2019 1 4 01.07.2019 01.12.2019 1 2 01.12.2019 01.01.2021 1 5 01.01.2021 01.01.2022

Now I want to create a table with the following content:

 Article ID Avg-Price in 2022 Avg-Price in 2021 Avg-Prive in 2020 Avg-Price in 2019 1

The problem is, I have years which are not mentioned in the data. In this case, for instance, year 2020 is not mentioned in the data, but has the price "2", because of "01.12.2019 to 01.01.2021". How can I solve this problem?

Is there a way to "split" each row in the data-table into multiple rows by containing year in the "valid from to valid to"-range?

For Example, lets look at row 3 in data-table:

 Article ID Price Price is valid from Price is valid to 1 2 01.12.2019 01.01.2021

I want to "split" this row in 3 different row, because this price is valid for 3 years:

 Article ID Price Price is valid from Price is valid to year 1 2 01.12.2019 01.01.2021 2019 1 2 01.12.2019 01.01.2021 2020 1 2 01.12.2019 01.01.2021 2021

Now i could calculate the avg price for all years because I have all relevant years in the new column "year"

Thank you!

Best

Labels (2)

• ### General Question

4 Replies
Contributor II
Author

Thank you

I see, that I can use a LOAD statement within a LOOP.

But how can I use a LOOP within a LOAD statement?

In my example mentioned above we have row number 3 (table 1) which results in 3 new rows (table 4), because the columns from - to are 2019 to 2021, which means I need the row with each 2019, 2020 and 2021.

I am still confused as a beginner.

Thanks!

Former Employee

The structure of a Load within a Loop is:
For ...
Next

The structure of a Loop within a Load is:
From ...
While ...;

The first blog post addresses both types.

@fatihu  try below

``````Load [Article ID],
Price,
Price_Valid_From,
Price_Valid_To,
Year(Price_Valid_From)+IterNo()-1 as Price_Valid_Year
FROM Source
while Year(Price_Valid_From)+IterNo()-1<=year(Price_Valid_To);``````
Tags
Community Browser