Firstsorted Value by Date and Time

[rustyfishbones]

Hi All,

I am trying to use an expression in a straight table to show the first transaction for each day.

I have the following Table

And I want the following result

So I need the first R transactions for each Day based on the time.

I hope you can help.

Date	Product	StartTime	Transaction Type	Amount
08/03/2017	BB4BL264R	07:02:38	R	1
08/03/2017	MAAX94S	07:03:17	R	1
08/03/2017	RB783351	07:03:27	D	1
09/03/2017	BB4BL238R	06:41:41	D	1
09/03/2017	RB786048	06:59:57	R	1
09/03/2017	RB786055	07:00:14	R	1
10/03/2017	JV60096	07:02:56	R	1
10/03/2017	JV60096	07:02:59	R	1
10/03/2017	MP002676	07:03:17	R	1
Date	Product	StartTime	Transaction Type	Amount
08/03/2017	BB4BL264R	07:02:38	R	1
09/03/2017	RB786048	06:59:57	R	1
10/03/2017	JV60096	07:02:56	R	1

[tresesco]

PFA

[rustyfishbones]

That does not return the correct Data?

[tresesco]

Ohh, you wanted first transaction. Try removing '-' like:

FirstSortedValue(Product, Time#(StartTime, 'hh:mm:ss'))

for time min() instead of max().

[effinty2112]

Hi Alan,

Maybe:

Date	Transaction Type	Product	StartTime	Amount
08/03/2017	R	BB4BL264R	07:02:38	1
09/03/2017	R	RB786048	06:59:57	1
10/03/2017	R	JV60096	07:02:56	1

Dims: Date and Transaction Type

Expressions:

Product: FirstSortedValue({$<[Transaction Type] = {'R'}>}Product,StartTime)

StartTime:Min({$<[Transaction Type] = {'R'}>}StartTime)

Amount:FirstSortedValue({$<[Transaction Type] = {'R'}>}Amount,StartTime)

Cheers

Andrew

[rustyfishbones]

It's almost there, I need to define a Transaction Type of R, at the moment I am getting the first transaction, but I need the first R transaction

[tresesco]

May be like:

FirstSortedValue({<[Transaction Type]={'R'}>} Product, Time#(StartTime, 'hh:mm:ss'))        , similarly for all

Time(min({<[Transaction Type={'R'}>} Time#(StartTime,'hh:mm:ss')))

[Kushal_Chawda]

script solution

Data:

LOAD Date,

           Product,

            time(StartTime) as StartTime,

           [Transaction Type],

           Amount

FROM Table;

left join(Data)

LOAD Date,

            time(min(StartTime)) as StartTime

            1 as MinTimeFlag

resident Data

group by Date;

you can now write the expression like below

=sum({<MinTimeFlag={1}>}Amount)

[Kushal_Chawda]

script solution

Data:

LOAD Date,

           Product,

            time(StartTime) as StartTime,

           [Transaction Type],

           Amount

FROM Table;

left join(Data)

LOAD Date,

            time(min(StartTime)) as StartTime

            1 as MinTimeFlag

resident Data

where [Transaction Type]='R'

group by Date;

you can now write the expression like below

=sum({<MinTimeFlag={1}>}Amount)

[rustyfishbones]

Hi,

I have tried each of the example and still can't get it.

Can I use in conjunction with Aggr, so I only have 1 expression and have Date, Product and StartTime as dimensions