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 ?
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;
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.
HI Manoranjan
PFA the .app
Regards
Pratyush
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
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;
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"
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.
Ok Kushal but please revert.
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
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.