Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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.
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
can u try this in qvw and provide me the qlikview file, since i tried this way but can't meet the output
can any one try this reply me?
Please check this file.
What is your source, database or Excel?
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
PFA the app with data files.
Regards
Pratyush
Excel only u can use the sample data.
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.