Concatenate - removing duplicate RowID

[#1]

Consider the following Script:

//***************** Cashflow ******************
Cashflow:
LOAD AUTONUMBER(TRIM(LOAN), EFF_DATE) AS AutoKey
,TRIM(LOAN) AS NativeLoanKey
,EFF_DATE AS Effective_Date
,PRIN_BAL AS Principal_Balance
,TRAN_AMT AS Amount;
SQL SELECT *
FROM dw_support.dbo.IRR_Transactions;

CONCATENATE(Cashflow)

LOAD AUTONUMBER(TRIM(LOAN), EFF_DATE) AS AutoKey
,TRIM(LOAN) AS NativeLoanKey
,EFF_DATE AS Effective_Date
,PRIN_BAL AS Principal_Balance
,TRAN_AMT AS Amount;
SQL SELECT *
FROM dw.dbo.IRR_LMSSummary;

It returns the following output.

What I want to see is during my load drop the second AutoKey = 65 so it only returns 1 row of data.

[Oleg_Troyansky]

Keyword DISTINCT can be used to remove duplicates. However, in your case, some of the fields (like Principal Balance) are not identical, so DISTINCT won't help. I'd say, you need to aggregate your end results by the set of "unique" keys and use functions like min(), max() or FirstSortedValue() to pick the desired value of the multiple available choices (first, last, highest, etc...)

[#3]

Still having issues even using FirstSortedValue, It might be how I use RESIDENT tables I am not exactly sure. I just don't know how to create the syntax I need to accomplish using FirstSortedValue once I have CONCATENATED the tables together.

[rwunderlich]

Do you need to use the two argument form of autonumber()? It looks like you are trying to keep only one combination of LOAN and EFF_DATE. I think you can do that with the single argument autonumber() and EXISTS like this:

Cashflow:
LOAD AUTONUMBER(TRIM(LOAN) & '|' & EFF_DATE) AS AutoKey
etc, etc

CONCATENATE(Cashflow)
LOAD AUTONUMBER(TRIM(LOAN) & '|' & EFF_DATE) AS AutoKey
etc, etc
WHERE NOT EXISTS(AutoKey)
;
SQL SELECT *
FROM dw.dbo.IRR_LMSSummary;

[#5]

I tried this as well. My concern with this method is that it drops off the last 2010-09-30 record for some reason, even though it doesn't exist yet. Otherwise I would have my results I am looking for...

[#6]

This is my revised code and it is not even close to working...

//***************** Cashflow ******************

Cashflow:
LOAD AUTONUMBER(TRIM(LOAN) & '|' & EFF_DATE) AS AutoKey
,TRIM(LOAN) AS NativeLoanKey
,EFF_DATE AS Effective_Date
,PRIN_BAL AS Principal_Balance
,TRAN_AMT AS Amount;
SQL SELECT *
FROM dw_support.dbo.IRR_Transactions;


LOAD AUTONUMBER(TRIM(LOAN) & '|' & EFF_DATE) AS AutoKey
,TRIM(LOAN) AS NativeLoanKey
,EFF_DATE AS Effective_Date
,PRIN_BAL AS Principal_Balance
,TRAN_AMT AS Amount;
SQL SELECT *
FROM dw.dbo.IRR_LMSSummary;

Final:

NOCONCATENATE
LOAD *
RESIDENT Cashflow
WHERE NOT EXISTS (AutoKey);

[rwunderlich · Accepted solution]

The WHERE NOT EXISTS belongs in the second load. (Forgot to mention repeating the autonumber). And you don't need the Final: load.

LOAD AUTONUMBER(TRIM(LOAN) & '|' & EFF_DATE) AS AutoKey
,TRIM(LOAN) AS NativeLoanKey
,EFF_DATE AS Effective_Date
,PRIN_BAL AS Principal_Balance
,TRAN_AMT AS Amount
WHERE NOT EXISTS (AutoKey, AUTONUMBER(TRIM(LOAN) & '|' & EFF_DATE) );
;
SQL SELECT *
FROM dw.dbo.IRR_LMSSummary;

[#8]

Thank-you for your help, it is working correctly now!