Hi All,
I have a problem where I want to exclude some rows from a table based on another table with the same fields. I try to use the Exists function but it seems not to be working as I would expect.
Let me explain:
I receive a daily file with a lot of transactions. The file name of the file is a date and a sequence number. I store the transactions in the file in a QVD called Facts that is growing slowly each day.
I do a simple:
tmp_files:
load *, Filename() as Filename, left(Filename,8) as FileDate from file
concatenate (tmp_files) load * from Facts.qvd
Store tmp_files into Facts.qvd
Now sometimes old transactions are changed in some way. Transactions that are already in my QVD and the source system will send me a new file with, not only the changed transaction, but also all other transactions belonging to the same date. The file name of this new file also consists of a date and a sequence number. But, the file date is exactly the same as the old file that should be replaced.
So, if I get a file with the file name 20160101_123 with 100 transactions then I get a new file with the name 20160101_987 with 120 transactions.
What I want to do is to find the 100 transactions with the file date 20160101 and exclude them from my Facts QVD and replace them with the new 120 transactions from the new file and then store the new table with replaced transactions as Facts.
Ok, to do this I first read the file and all its transactions into memory:
Tab1:
FileName, FileDate, Data1, Data2
20160101_987,20160101,123,123
20160101_987,20160101,234,234
20160101_987,20160101,234,435
And then read my Facts QVD into memory but here I would like to use the where "not exists" function to exclude the transactions immediately:
Facts:
noconcatenate load * from Facts.qvd where not exists(FileDate)
QVD is stored like this:
FileName, FileDate, Data1, Data2
20160101_665,20160101,123,123
20160101_665,20160101,234,234
20160101_665,20160101,234,435
20160102_680,20160102,234,435
20160103_685,20160103,234,435
But as you already suspected this doesn't work. What I get in Facts is the first occurrence of a FileDate so I only get one transaction per date. I've tried different ways to use the Exists here but it doesn't seem to give the result that I want.
I would like my Facts to result in this:
FileName, FileDate, Data1, Data2
20160102_680,20160102,234,435
20160103_685,20160103,234,435
I would then just concatenate and store the qvd like this:
concatenate(Tab1) load * resident Facts;
Drop table Facts;
Store Tab1 into Facts.qvd(qvd);
Drop table Tab1;
Any suggestions how to solve this? It seems very obvious that I don't know how to use the Exists function. Are there other better ways to replace the transactions?
Thanks in advance
Br
Cris
