Incremental load for maintaining only insert and updated records in seperate table

[manoranjan_d]

Hi all,

In the incremental load insert and update are happening in single table, but my client is saying insert have to separate table and updation have to be in separate table. how to do this?

example below is sample data(source data)

ID	Name	Event	Ratio	date
1	xxx1	A	100	01-06-17
2	xxx2	B	200	02-06-17
3	xxx3	C	300	03-06-17
4	xxx4	A	400	04-06-17
5	xxx5	B	500	05-06-17
6	xxx6	C	600	06-06-17
7	xxx7	A	700	07-06-17
8	xxx8	B	800	08-06-17
9	xxx9	C	900	09-06-17
10	xxx10	A	1000	10-06-17

Requirement1:

Now suppose a new record have been inserted in the above source  data table

example

11 XXX1 A 1100 20-06-17

then the insert table in qlikview should have to  be in the below output

output of insert table:

ID	Name	Event	Ratio	date
1	xxx1	A	100	01-06-17
2	xxx2	B	200	02-06-17
3	xxx3	C	300	03-06-17
4	xxx4	A	400	04-06-17
5	xxx5	B	500	05-06-17
6	xxx6	C	600	06-06-17
7	xxx7	A	700	07-06-17
8	xxx8	B	800	08-06-17
9	xxx9	C	900	09-06-17
10	xxx10	A	1000	10-06-17
11	xxx11	A	1100	20-06-17

so the insert table should have old records + new reocrds

Requirement 2:

Now suppose a old record have been updated in the above source data table

example

1 XXX1 A 2000 20-06-17

then the update table in qlikview should  have to be  in the below output

output of Update table:

ID	Name	Event	Ratio	date
1	xxx1	A	100	01-06-17
1	XXX1	A	2000	20-06-17

So the update table should have the old record + new updated  record.

How achieve this ?

[Kushal_Chawda]

First Store your data into QVD

Data:

ID

Name

Event

Ratio

date

FROM Excel

Store Data into ..\Fact.QVD(qvd);

Drop table Data;

MaxDate:

LOAD max(date) as MaxDate

FROM Fact.Qvd(qvd);

let vMaxDate = peek('MaxDate',0'MaxDate');

Insert:

LOAD *

FROM Fact.Qvd(qvd);

Concatenate(Insert)

LOAD *

FROM Excel

where not exist (ID);

Store Insert into ..\Fact_Insert.QVD(qvd);

Drop table Insert ;

Fact:

LOAD *

FROM Fact.Qvd(qvd);

Update:

LOAD *

FROM Excel

where floor(date)>$(vMaxDate) and exists(ID);

Drop table Fact;

Concatenate(Update)

LOAD *

FROM Fact.Qvd(qvd)

where exists(ID);

Store Update into ..\Fact_Update.QVD(qvd);

Drop table Update;

[manoranjan_d]

hi

Fact:

LOAD *

FROM Fact.Qvd(qvd);

Update:

LOAD *

FROM Excel

where floor(date)>$(vMaxDate) and exists(ID);

Drop table Fact;

Concatenate(Update)

LOAD *

FROM Fact.Qvd(qvd)

where exists(ID);

Store Update into ..\Fact_Update.QVD(qvd);

Drop table Update;

when u do this u will get new record+updateed record also where floor(date)>$(vMaxDate) and exists(ID); i need the uopdated record only.

[prat1507]

HI Manoranjan

PFA the .app

Regards
Pratyush

[manoranjan_d]

y u are using two data files as excel?

y u r maintaining the update and new record in excel as data2...we have only one source as data 1

[Kushal_Chawda]

First time create QVD from Excel and then comment the script as from next load incremental logic will work

MaxDate:

LOAD max(date) as MaxDate

FROM Fact.Qvd(qvd);

let vMaxDate = peek('MaxDate',0'MaxDate');

Insert:

LOAD *

FROM Fact.Qvd(qvd);

Concatenate(Insert)

LOAD *

FROM Excel

where not exist (ID);

Store Insert into ..\Fact_Insert.QVD(qvd);

Drop table Insert ;

Fact:

LOAD *

FROM Fact.Qvd(qvd);

Update:

NOCONCATENATE

LOAD *

FROM Excel

where floor(date)> '$(vMaxDate)' and exists(ID);

Drop table Fact;

Concatenate(Update)

LOAD *

FROM Fact.Qvd(qvd)

where exists(ID);

Store Update into ..\Fact_Update.QVD(qvd);

Drop table Update;

Data:

LOAD *

FROM Fact_Insert.QVD(qvd);

concatenate(Final)

LOAD *

FROM Fact_Update.QVD(qvd);

Final:

NOCONCATENATE

LOAD DISTINCT *

Resident Data;

Drop table Data;

Store Final into ..\Fact.QVD(qvd);

Drop table Final;

[prma7799]

kush141087‌,

Is there any wrong approach in my solution.

I am also did like you said .

"First time create QVD from Excel and then comment the script as from next load incremental logic will work"

[Kushal_Chawda]

Hi bro, I have not checked your solution, I will look into it. As long as your approach is giving correct results, there should not have any issue to follow that approach.

[prma7799]

Ok Kushal but please revert.

[Kushal_Chawda]

see this attached file, which works fine for me

Data Excel file contains new insert and update data

Fact QVD conatins old data

See the results in QV app

[Kushal_Chawda · Accepted solution]

manoranjan.d‌  if you got the answer then please close the thread by marking answer as correct. I have seen that most of your threads are not closed.