Filter using Max in Load Script

[JuMacedo]

Hi!

I'm trying to filter a table while loading it from a sql database, all I want is that it loads just the rows where BatchId is the last one.

This is how the script looks like:

LOAD BatchId, 

ProcessId, 

SourceProcessId, 

StartDatum;

[Process]:

SELECT BatchId,

ProcessId,

SourceProcessId,

StartDatum

FROM "databasename".dbo."Process";

Temp:

LOAD

Max(BatchId) as MaxBatchId,

ProcessId

Resident Process

Group by ProcessId;

This is script doesn't work and I don't understand why. If do do only this:

Temp:

LOAD

Max(BatchId) as MaxBatchId,

Resident Process;

It works fine, it creates a table where only the last Batch appears. But if I execute the previous script I get the same number of rows as the original and all BatchId, not only the last one.

Any clues??

[JonnyPoole]

In your first attempt, the Max(BatchId) should have returned a list of all processIDs and next to each processID should have been the highest batchID value found across all records with the same processID. 

So if your source data was this: 

BatchID,ProcessID

5,A

3,A

4,A

2,B

1,B

3,B

the first attempt should return 

BatchID,ProcessID

5,A

3,B

You said you wanted the 'last' one. Does that mean you wanted to get the last batchID for each processID in the order that the data is loaded? Like this?

BatchID,ProcessID

4,A

3,B

Just want to see the exact result you are after. If you can share a sample with data and the desired result that would be helpful.

[BrunPierre · Accepted solution]

Do this;

[Process]:

LOAD BatchId, 

ProcessId, 

SourceProcessId, 

StartDatum;

SELECT BatchId,

ProcessId,

SourceProcessId,

StartDatum

FROM "databasename".dbo."Process";

Inner Join

LOAD

Max(BatchId) as BatchId,

ProcessId

Resident Process

Group by ProcessId;

[JuMacedo]

Hi, 

The result I'm looking for is this as per your example:

BatchID,ProcessID

5,A

3,B

I want the last BatchId processed, not the last row with batchId. Unforttunatly I can't share any data.

[JuMacedo]

Thank you so much, it did work!