topic Re: Extract all year dates between two dates in a table in App Development

Extract all year dates between two dates in a table

fatihu — Fri, 02 Jun 2023 13:52:46 GMT

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"

Can someone please help me?

Thank you!

Best

Re: Extract all year dates between two dates in a table

hic — Fri, 02 Jun 2023 13:28:00 GMT

A "While" clause will do what you want. See
https://community.qlik.com/t5/Design/Loops-in-the-Script/ba-p/1473543
or
https://community.qlik.com/t5/Design/Creating-Reference-Dates-for-Intervals/ba-p/1463944

Re: Extract all year dates between two dates in a table

fatihu — Fri, 02 Jun 2023 13:56:22 GMT

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!

Re: Extract all year dates between two dates in a table

hic — Fri, 02 Jun 2023 14:10:54 GMT

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

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

The first blog post addresses both types.

Re: Extract all year dates between two dates in a table

Kushal_Chawda — Fri, 02 Jun 2023 22:51:03 GMT

@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);