Appending Data with Previous loaded data While extracting data from Database

[DS14]

Hi Experts

I have to schedule the extractor in way that it reload the data every month from database and concatenate the data every month with previous loaded data in qvd.

I have to store the data monthwise in same qvd when the extractor reloads.

For Example

This is the data for the month March'19

Employee ID	P A	EMP Name	D.O.J	Date of Birth	Calendar Hours	Paid Hours	LOP in Hours	Calendar days	Designation	Basic	Month	Year
10000001	PA00	A	01-02-1999	05-12-1949	248	248	0.00	31	Managing Director	16,74,656.00	March	2019
10000008	PA00	B	19-12-1984	01-04-1964	248	246.5	1.50	31	Manager - Credit Control	24,873.64	March	2019
10000009	PA09	C	29-04-1985	05-05-1962	248	248	0.00	31	Deputy Manager	26,500.00	March	2019
10000011	PA10	D	01-02-1986	26-02-1963	248	248	0.00	31	Assistant Manager - Production	14,900.00	March	2019
10000013	PA09	E	05-03-1988	10-05-1961	248	248	0.00	31	Senior Supervisor	18,050.00	March	2019
10000014	PA10	F	05-07-1988	05-04-1970	248	248	0.00	31	Assistant Manager	15,780.00	March	2019
10000015	PA02	G	11-07-1989	27-07-1971	248	248	0.00	31	Senior Executive	24,350.00	March	2019
10000017	PA03	H	20-12-1991	20-07-1971	248	235	13.00	31	Assistant General Manager	46,175.60	March	2019
10000020	PA09	I	01-11-1992	28-01-1969	248	248	0.00	31	Senior Executive	20,500.00	March	2019

Next Month when the extractor reloads it will extract the data for the April'19 for the same records. So  I want to append that data with (Existing Data) March'19 Data.  Let the data for April be

Employee ID	P A	EMP Name	D.O.J	Date of Birth	Calendar Hours	Paid Hours	LOP in Hours	Calendar days	Designation	Basic	Month	Year
10000001	PA00	A	01-02-1999	05-12-1949	248	248	0.00	31	Managing Director	16,74,656.00	April	2019
10000008	PA00	B	19-12-1984	01-04-1964	248	246.5	1.50	31	Manager - Credit Control	24,873.64	April	2019
10000009	PA09	C	29-04-1985	05-05-1962	248	248	0.00	31	Deputy Manager	46,175.60	April	2019
10000011	PA10	D	01-02-1986	26-02-1963	248	248	0.00	31	Assistant Manager - Production	14,900.00	April	2019
10000013	PA09	E	05-03-1988	10-05-1961	248	248	1.50	31	Senior Supervisor	18,050.00	April	2019
10000014	PA10	F	05-07-1988	05-04-1970	248	248	0.00	31	Assistant Manager	14,900.00	April	2019
10000015	PA02	G	11-07-1989	27-07-1971	248	248	0.00	31	Senior Executive	24,350.00	April	2019
10000017	PA03	H	20-12-1991	20-07-1971	248	235	13.00	31	Assistant General Manager	46,175.60	April	2019
10000020	PA09	I	01-11-1992	28-01-1969	248	248	1.50	31	Senior Executive	20,500.00	April	2019

Output:

The Output Will be

Employee ID	P A	EMP Name	D.O.J	Date of Birth	Calendar Hours	Paid Hours	LOP in Hours	Calendar days	Designation	Basic	Month	Year
10000001	PA00	A	01-02-1999	05-12-1949	248	248	0.00	31	Managing Director	16,74,656.00	March	2019
10000008	PA00	B	19-12-1984	01-04-1964	248	246.5	1.50	31	Manager - Credit Control	24,873.64	March	2019
10000009	PA09	C	29-04-1985	05-05-1962	248	248	0.00	31	Deputy Manager	26,500.00	March	2019
10000011	PA10	D	01-02-1986	26-02-1963	248	248	0.00	31	Assistant Manager - Production	14,900.00	March	2019
10000013	PA09	E	05-03-1988	10-05-1961	248	248	0.00	31	Senior Supervisor	18,050.00	March	2019
10000014	PA10	F	05-07-1988	05-04-1970	248	248	0.00	31	Assistant Manager	15,780.00	March	2019
10000015	PA02	G	11-07-1989	27-07-1971	248	248	0.00	31	Senior Executive	24,350.00	March	2019
10000017	PA03	H	20-12-1991	20-07-1971	248	235	13.00	31	Assistant General Manager	46,175.60	March	2019
10000020	PA09	I	01-11-1992	28-01-1969	248	248	0.00	31	Senior Executive	20,500.00	March	2019
10000001	PA00	A	01-02-1999	05-12-1949	248	248	0.00	31	Managing Director	16,74,656.00	April	2019
10000008	PA00	B	19-12-1984	01-04-1964	248	246.5	1.50	31	Manager - Credit Control	24,873.64	April	2019
10000009	PA09	C	29-04-1985	05-05-1962	248	248	0.00	31	Deputy Manager	46,175.60	April	2019
10000011	PA10	D	01-02-1986	26-02-1963	248	248	0.00	31	Assistant Manager - Production	14,900.00	April	2019
10000013	PA09	E	05-03-1988	10-05-1961	248	248	1.50	31	Senior Supervisor	18,050.00	April	2019
10000014	PA10	F	05-07-1988	05-04-1970	248	248	0.00	31	Assistant Manager	14,900.00	April	2019
10000015	PA02	G	11-07-1989	27-07-1971	248	248	0.00	31	Senior Executive	24,350.00	April	2019
10000017	PA03	H	20-12-1991	20-07-1971	248	235	13.00	31	Assistant General Manager	46,175.60	April	2019
10000020	PA09	I	01-11-1992	28-01-1969	248	248	1.50	31	Senior Executive	20,500.00	April	2019

This method should be followed every Month. Every month data will be concatenated with previous data.

I am attaching the sample data for reference.

Please suggest.

Help would be appreciated.

Thanks in Advance.

[JordyWegman]

Hi Deepaksingh,

This should be fairly easy to setup:

TableWithOldQvd:
Load
    *
From [Your source];

// Create part for your calendar to know which month it is
tmpCalendar:
    Month
From [Calendar source]
Where Date = (Today()
;

let vCurrentMonth = peek('Month',0,'Calendar')

drop table tmpCalendar;

Concatenate(TableWithOldQvd)
Load
    *
From [Connection with database]
Where Month = $(vCurrentmonth);

But a better thing to do is make a table key. A unique string that only is there once in your table. The reason for this is that all new entries are concatenated to your source.

// Create a unique key and name it %{YourTableName}, like %TableWithOldQvd.
// This can be a combination of EmployeeID, Month and Year.
TableWithOldQvd:
Load
    EmployeeID &'|'& Month  &'|'& Year as %TableWithOldQvd
    *
From [Your source];

Concatenate(TableWithOldQvd)
Load
    *
Where not Exists (%TableWithOldQvd)
;
Load
    EmployeeID &'|'& Month &'|'& Year as %TableWithOldQvd
    *
From [Connection with database]
;

Jordy 

Climber