How do I display the record with the minimum date

[#1]

I know that this must involve the FirstSortedValue function and possibly the Aggr function as well...

First, I don't have control over the Load as this is QlikView Advanced Reporting in Quickbooks.. 

In a Straight Table, I'm trying to display only the record for each SO # that has the earliest Txn Date. For the Amount field, I have the Expression built this way:    sum({$<[Transactions.Txn Type]={'Invoice'}>} Transactions.Amount)

So for SO# 6922-1, I'd want to see only one record since the same date displays twice. For SO# 6922-2, I'd want to see one of the records that has the 3/23/2017 date.

Txn Type	Item Type	PO #	SO #	Txn Date	Amount
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-1	03/23/2017	$926.40
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-1	03/23/2017	$1,890.40
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-2	04/30/2017	$360.52
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-2	03/23/2017	$926.40
Invoice	ServiceItem	7FEB2017DJACOBSON	6922-2	03/23/2017	$1,890.40
Invoice	ServiceItem	P2546	6953	04/21/2017	$1,134.54
Invoice	ServiceItem	P2546	6953	05/25/2017	$1,134.54
Invoice	ServiceItem	2045184	6972	04/19/2017	$926.40
Invoice	ServiceItem	2045296	6978	04/28/2017	$926.40
Invoice	ServiceItem	2045296	6978	04/26/2017	$1,890.40
Invoice	ServiceItem	2045472	6980	05/05/2017	$1,890.40
Invoice	ServiceItem	2045610	6991	05/18/2017	$463.20
Invoice	ServiceItem	2045610	6991	05/18/2017	$945.20
Invoice	ServiceItem	2045755	6995	05/24/2017	$1,890.40
Invoice	ServiceItem	2045860	7005	05/31/2017	$463.20
Invoice	ServiceItem	2045860	7005	05/31/2017	$945.20
Invoice	ServiceItem	2045898	7018	06/15/2017	$926.40
Invoice	ServiceItem	2046419	7037	07/06/2017	$463.20
Invoice	ServiceItem	2046419	7037	07/06/2017	$945.20
Invoice	ServiceItem	2046573	7052	07/19/2017	$926.40

[Anil_Babu_Samineni]

May be this?

sum({$<[Transactions.Txn Type]={'Invoice'}, [Txn Date] = {'$(=Date(Min([Txn Date]),'MM/DD/YYYY'))'}>} Transactions.Amount)

[#3]

I tried your suggestion, Anil, but unfortunately, the data came back with all of the rows. Thank you, though.

[Anil_Babu_Samineni]

Should work, for 6922-1 which date amount you need to pick. If this condition won't work that mean your date format is not good.

[#5]

For 6922-1, it doesn't matter which record is displayed. I don't care which Amount is displayed, I just need one record for each SO #.  Actually, I don't even need the Amount displayed, but wasn't sure how to make the Txn Date field an expression.

Unfortunately, I can't control the format for the date as I don't have the ability to control the Load statement.

[Anil_Babu_Samineni]

Great, I thought you are doing this in Set analysis

May be try this

Only({$<[Transactions.Txn Type]={'Invoice'}, [Txn Date] = {'$(=Date(Min([Txn Date]),'MM/DD/YYYY'))'}>} [Txn Date])

[antoniotiman]

Hi Chris,

try this

SET DateFormat='MM/DD/YYYY';
LOAD [Txn Type],
[Item Type],
[PO #],
[SO #],
[Txn Date],
PurgeChar(Amount,'$') as Amount
FROM
"https://community.qlik.com/message/1325946"
(html, codepage is 1252, embedded labels, table is @1);

Regards,

Antonio

[#8]

Thanks, Antonio, but I can't control the Load as I'm using Advanced Reporting in Quickbooks which is the front end of QlikView.

[#9]

Anil - should this be the formula for the Txn Date Dimension? If so, the syntax isn't working.

[vishsaggi]

Are you looking for this?

Expr:

= FirstSortedValue(TxnDate, Aggr(TxnDate, TxnType, ItemType, SO#, TxnDate))

= FirstSortedValue(Amount, Aggr(Sum(Amount), TxnType, ItemType, SO#, TxnType))