topic Re: Incremental load for maintaining only insert and updated records in seperate table in QlikView

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

manoranjan_d — Wed, 21 Jun 2017 06:25:34 GMT

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 ?

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

prat1507 — Wed, 21 Jun 2017 06:35:20 GMT

May be this

//For Data Update table

No Concatenate Update:

load Id, Name, Event, Ratio, Date

From 'Data Source'

Where Exists(Id);

//For Data Insert table

Concatenate Insert :

load Id, Name, Event, Ratio, Date

From 'Data Source'

Where Not Exists(Id);

Regards

Pratyush

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

manoranjan_d — Wed, 21 Jun 2017 06:47:02 GMT

can u try this in qvw and provide me the qlikview file, since i tried this way but can't meet the output

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

manoranjan_d — Wed, 21 Jun 2017 07:15:02 GMT

can any one try this reply me?

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

prma7799 — Wed, 21 Jun 2017 07:52:49 GMT

Please check this file.

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

Kushal_Chawda — Wed, 21 Jun 2017 08:06:25 GMT

What is your source, database or Excel?

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

manoranjan_d — Wed, 21 Jun 2017 08:06:54 GMT

in ur qlikview ur output isnot meeting just check the requirement2

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

we are getting only one sno id for 1

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

prat1507 — Wed, 21 Jun 2017 08:12:30 GMT

PFA the app with data files.

Regards
Pratyush

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

manoranjan_d — Wed, 21 Jun 2017 09:52:13 GMT

Excel only u can use the sample data.

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

manoranjan_d — Wed, 21 Jun 2017 09:56:10 GMT

Hi prat1507

i need only two tables in the data model. understand my requirement pls

see source we have 10 records

tomo i going to add new record as 11 so as per the req1 we need old + new reords so we can achive this in a simple way

now coming req2:

day after tomo i m updating the old record exaple ID 1 , so my client want to compare the old record+ new updated records

these two req has to be sep table.

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

Kushal_Chawda — Wed, 21 Jun 2017 10:22:49 GMT

First Store your data into QVD

Data:

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;

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

manoranjan_d — Wed, 21 Jun 2017 10:42:23 GMT

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.

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

prat1507 — Wed, 21 Jun 2017 10:59:06 GMT

HI Manoranjan

PFA the .app

Regards
Pratyush

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

manoranjan_d — Wed, 21 Jun 2017 11:07:29 GMT

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

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

Kushal_Chawda — Wed, 21 Jun 2017 11:09:07 GMT

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;

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

prma7799 — Wed, 21 Jun 2017 11:18:02 GMT

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"

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

Kushal_Chawda — Wed, 21 Jun 2017 11:21:28 GMT

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.

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

prma7799 — Wed, 21 Jun 2017 11:33:27 GMT

Ok Kushal but please revert.

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

Kushal_Chawda — Wed, 21 Jun 2017 11:56:49 GMT

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

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

Kushal_Chawda — Tue, 27 Jun 2017 07:44:49 GMT

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.