There is a shorter way, but it is usable if you don't need to keep the original "INPUT" table:
LOAD * INLINE [
ORDER_ID, OPERATION_No, TransferDate
10001, 200, 15.01.2013
10002, 100, 19.01.2013
10001, 300, 19.01.2013
10001, 40, 16.01.2013
10001, 400, 19.01.2013
10002, 200, 15.01.2013
10002, 300, 23.01.2013
10001, 100, 19.01.2013
10002, 40, 23.01.2013
10002, 400, 23.01.2013];
INNER JOIN (INPUT) LOAD DISTINCT
max(TransferDate) AS TransferDate
GROUP BY ORDER_ID;
thank you for your contribution. I didn't thought of inner join...
I think a shorter way (in lines of code) than yours is not possible. At the moment there is no need to keep the original table, so it would be possible from that view. But I'm always cautious when it comes to joins, because my structure is "a bit" more complex than this test example. I will take a look which solution performs better.
In your case I have to load the complete table first and then apply the inner join. In my case I load the (small) mapping table first and then the necessary data via Where clause.(I don't have the table already loaded like in this example). I haven't the experience to tell which way is more efficient(speed / ram usage).The table is not that big at the moment it has about 20 cols and 200k rows. I hope I can measure a difference between the 2 solutions.
Anyway, its very helpful, I always asked myself in the past when(/where) I would need the inner join.
As per you source data nature the new data just append to QVD and it is Source.QVD.
Here I am creating intermediate QVD to avoid the full scan of Source qvd.
LET vNow= number(now()) ;
IF Transform.qvd exists // Loading new data from source after last reload + transfrom qvd
LOAD *, OrderID&'-'OperationNO AS PK FROM Source.qvd Where Datefiled >= $(vLastReload );
LOAD * from Transform.qvd where not exists (PK,OrderID&'-'&OperationNo);
Inner Join (Transform)
Load OrderID, Max(DateField) Resident Transform group by OrderID;
ELSE //Full scan on Source.qvd
LOAD * FROM Source.qvd;
Inner Join (Transform)
LOAD OrderID, max(Datefiled) AS Datefield
From Source.qvd Group by OrderID ;
STORE Transform into Transform.qvd (qvd);
Let vLastReload = $(vNow);
You can tweak this idea with your example.
Please post same qvd data if you have any questions so any one from community will help better
thank you for your reply and the script. Its much more than I expected.
If I understand this script right, its more an addition to the original problem. The cleanup itself happens with the inner join like Michael suggested?
But it could be relevant if the data is growing. And it looks easier as I thought it would be..
I think instead of now(), I have to use today(). I don't have a timestamp in the source, just a date like "19.01.2013". Or is it possible to use something like if 19.01.2001 >= now() than ....