Multiple year data into single qvd

[Tool_Tip]

Dear all,

I have table as below in our database:

ID	Amount	Year
1	100	2000
2	200	2001
3	400	2000
4	1000	2002
5	4343	2000
6	4566	2002
7	6878	2001
8	13223	2000
9	97779	2002
10	232	2002
11	354	2001

We want to create single QVD by loading each year data and concatenating using for loop. How can we achieve this.

Note: this is just an example, we have 30 million record in an table so don't want load table directly.

[MK_QSL]

AllYears:

Load Distinct Year from YourDatabaseTable;

For vYearNo = 0 to NoOfRows('AllYears')

    Let vYear = Peek('Year', $(vYearNo), 'AllYears');

    DATA:

    Load * From YourDatabaseTable Where Year = '$(vYear)';

    Let vYear = Null();

Next vYearNo

Store DATA Into YourFinalTableName.qvd(QVD);

Drop Table DATA:

Drop Table AllYears;

[MK_QSL]

If you want to store QVD by year then use as below.

AllYears:

Load Distinct Year from YourDatabaseTable;

For vYearNo = 0 to NoOfRows('AllYears')

    Let vYear = Peek('Year', $(vYearNo), 'AllYears');

    DATA:

    Load * From YourDatabaseTable Where Year = '$(vYear)';

    Store DATA Into YourFinalTableName_$(vYear).qvd(QVD);

    Drop Table DATA:

    Let vYear = Null();

Next vYearNo

Drop Table AllYears;

[LoKi_asterix]

@MK_QSL  How can I dynamically store data from the last 3 years in a single QVD?.

[MK_QSL]

Not clear from your requirement, what exactly you wan to do?

Do you want 

1) To store individual QVD by year and one QVD having last 3 years data?

2) To load only last three years data and store in QVD?

Please describe.

[LoKi_asterix]

To store individual QVD by year and one QVD having the last 3 years' data to meet the requirement for rolling 3-year trend analysis.

[chriscammers]

When you went to the trouble of looping over all the years to make your partitioned tables it does not really make sense to combine them again when you can do a similar loop to load the most recent 3 years based on the year of the current day.

You can set the number of years like a parameter so when the requirements change and your users want more data you can do it without having to change more thant the value of the variable.

[LoKi_asterix]

Thank you for the clarification. Could you please provide an example or show how to set up the loop to load the most recent 3 years dynamically using a parameter? It would help me implement it correctly.