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 ?

[prat1507]

Hi

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

[manoranjan_d]

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

[manoranjan_d]

can any one try this reply me?

[prma7799]

Please check this file.

[Kushal_Chawda]

What is your source, database or Excel?

[manoranjan_d]

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

[prat1507]

PFA the app with data files.

Regards
Pratyush

[manoranjan_d]

Excel only u can use the sample data.

[manoranjan_d]

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.