Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rustyfishbones
Master II
Master II

Firstsorted Value by Date and Time

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

2017-08-24_1104.png

And I want the following result

2017-08-24_1105.png

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

I hope you can help.

                                                                                                                                                              

DateProductStartTimeTransaction TypeAmount
08/03/2017BB4BL264R07:02:38R1
08/03/2017MAAX94S07:03:17R1
08/03/2017RB78335107:03:27D1
09/03/2017BB4BL238R06:41:41D1
09/03/2017RB78604806:59:57R1
09/03/2017RB78605507:00:14R1
10/03/2017JV6009607:02:56R1
10/03/2017JV6009607:02:59R1
10/03/2017MP00267607:03:17R1
DateProductStartTimeTransaction TypeAmount
08/03/2017BB4BL264R07:02:38R1
09/03/2017RB78604806:59:57R1
10/03/2017JV6009607:02:56R1
1 Solution

Accepted Solutions
sunny_talwar

Another option

Dimension

Date

Transaction Type

Expressions

Only({<[Transaction Type] = {'R'}>}Aggr(If(StartTime = Min(TOTAL <Date> {<[Transaction Type] = {'R'}>} StartTime), Only({<[Transaction Type] = {'R'}>}Product)), Product, Date, StartTime))

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

Sum({<[Transaction Type] = {'R'}>}Aggr(If(StartTime = Min(TOTAL <Date> {<[Transaction Type] = {'R'}>} StartTime), Sum({<[Transaction Type] = {'R'}>}Amount)), Product, Date, StartTime))

Used tresesco‌'s sample file to create a new chart (thanks)

Capture.PNG

Best,

Sunny

View solution in original post

19 Replies
tresesco
MVP
MVP

Capture.PNG

PFA

rustyfishbones
Master II
Master II
Author

That does not return the correct Data?

tresesco
MVP
MVP

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

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

for time min() instead of max().

Capture.PNG

effinty2112
Master
Master

Hi Alan,

Maybe:

Date Transaction Type Product StartTime Amount
08/03/2017RBB4BL264R07:02:381
09/03/2017RRB78604806:59:571
10/03/2017RJV6009607: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
Master II
Master II
Author

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

2017-08-24_1134.png

tresesco
MVP
MVP

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

Capture.PNG

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
Master II
Master II
Author

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