Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

Capture.PNG

PFA

Master II
Master II

That does not return the correct Data?

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

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

Master II
Master II

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

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

MVP
MVP

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)

MVP
MVP

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)

Master II
Master II

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