topic Concatenate - removing duplicate RowID in QlikView

Concatenate - removing duplicate RowID

Mon, 21 Mar 2011 14:57:49 GMT

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.

Concatenate - removing duplicate RowID

Oleg_Troyansky — Mon, 21 Mar 2011 15:17:20 GMT

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...)

Concatenate - removing duplicate RowID

Mon, 21 Mar 2011 17:03:40 GMT

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.

Concatenate - removing duplicate RowID

rwunderlich — Mon, 21 Mar 2011 18:14:42 GMT

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;

Concatenate - removing duplicate RowID

Mon, 21 Mar 2011 18:25:21 GMT

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...

Concatenate - removing duplicate RowID

Mon, 21 Mar 2011 18:35:10 GMT

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);

Concatenate - removing duplicate RowID

rwunderlich — Mon, 21 Mar 2011 18:41:03 GMT

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;

Concatenate - removing duplicate RowID

Mon, 21 Mar 2011 20:07:07 GMT

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