Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Partner
Partner

Appending Data with Previous loaded data While extracting data from Database

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 IDP AEMP NameD.O.JDate of BirthCalendar HoursPaid HoursLOP in HoursCalendar daysDesignationBasicMonthYear
10000001PA00A01-02-199905-12-19492482480.0031Managing Director16,74,656.00March2019
10000008PA0019-12-198401-04-1964248246.51.5031Manager - Credit Control24,873.64March2019
10000009PA09C29-04-198505-05-19622482480.0031Deputy Manager26,500.00March2019
10000011PA10D01-02-198626-02-19632482480.0031Assistant Manager - Production14,900.00March2019
10000013PA09E05-03-198810-05-19612482480.0031Senior Supervisor18,050.00March2019
10000014PA10F05-07-198805-04-19702482480.0031Assistant Manager15,780.00March2019
10000015PA02G11-07-198927-07-19712482480.0031Senior Executive24,350.00March2019
10000017PA03H20-12-199120-07-197124823513.0031Assistant General Manager46,175.60March2019
10000020PA09I01-11-199228-01-19692482480.0031Senior Executive20,500.00March2019

 

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 IDP AEMP NameD.O.JDate of BirthCalendar HoursPaid HoursLOP in HoursCalendar daysDesignationBasicMonthYear
10000001PA00A01-02-199905-12-19492482480.0031Managing Director16,74,656.00April2019
10000008PA0019-12-198401-04-1964248246.51.5031Manager - Credit Control24,873.64April2019
10000009PA09C29-04-198505-05-19622482480.0031Deputy Manager46,175.60April2019
10000011PA10D01-02-198626-02-19632482480.0031Assistant Manager - Production14,900.00April2019
10000013PA09E05-03-198810-05-19612482481.5031Senior Supervisor18,050.00April2019
10000014PA10F05-07-198805-04-19702482480.0031Assistant Manager14,900.00April2019
10000015PA02G11-07-198927-07-19712482480.0031Senior Executive24,350.00April2019
10000017PA03H20-12-199120-07-197124823513.0031Assistant General Manager46,175.60April2019
10000020PA09I01-11-199228-01-19692482481.5031Senior Executive20,500.00April2019

 

Output:

The Output Will be

Employee IDP AEMP NameD.O.JDate of BirthCalendar HoursPaid HoursLOP in HoursCalendar daysDesignationBasicMonthYear
10000001PA00A01-02-199905-12-19492482480.0031Managing Director16,74,656.00March2019
10000008PA0019-12-198401-04-1964248246.51.5031Manager - Credit Control24,873.64March2019
10000009PA09C29-04-198505-05-19622482480.0031Deputy Manager26,500.00March2019
10000011PA10D01-02-198626-02-19632482480.0031Assistant Manager - Production14,900.00March2019
10000013PA09E05-03-198810-05-19612482480.0031Senior Supervisor18,050.00March2019
10000014PA10F05-07-198805-04-19702482480.0031Assistant Manager15,780.00March2019
10000015PA02G11-07-198927-07-19712482480.0031Senior Executive24,350.00March2019
10000017PA03H20-12-199120-07-197124823513.0031Assistant General Manager46,175.60March2019
10000020PA09I01-11-199228-01-19692482480.0031Senior Executive20,500.00March2019
10000001PA00A01-02-199905-12-19492482480.0031Managing Director16,74,656.00April2019
10000008PA0019-12-198401-04-1964248246.51.5031Manager - Credit Control24,873.64April2019
10000009PA09C29-04-198505-05-19622482480.0031Deputy Manager46,175.60April2019
10000011PA10D01-02-198626-02-19632482480.0031Assistant Manager - Production14,900.00April2019
10000013PA09E05-03-198810-05-19612482481.5031Senior Supervisor18,050.00April2019
10000014PA10F05-07-198805-04-19702482480.0031Assistant Manager14,900.00April2019
10000015PA02G11-07-198927-07-19712482480.0031Senior Executive24,350.00April2019
10000017PA03H20-12-199120-07-197124823513.0031Assistant General Manager46,175.60April2019
10000020PA09I01-11-199228-01-19692482481.5031Senior Executive20,500.00April2019

 

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.

 

1 Reply
Partner
Partner

Re: Appending Data with Previous loaded data While extracting data from Database

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

Work smarter, not harder