topic Firstsorted Value by Date and Time in QlikView

Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 10:07:20 GMT

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

Re: Firstsorted Value by Date and Time

tresB — Thu, 24 Aug 2017 10:18:46 GMT

PFA

Re: Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 10:21:41 GMT

That does not return the correct Data?

Re: Firstsorted Value by Date and Time

tresB — Thu, 24 Aug 2017 10:27:37 GMT

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

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

for time min() instead of max().

Re: Firstsorted Value by Date and Time

effinty2112 — Thu, 24 Aug 2017 10:34:17 GMT

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

Re: Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 10:35:37 GMT

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

Re: Firstsorted Value by Date and Time

tresB — Thu, 24 Aug 2017 10:38:34 GMT

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

Re: Firstsorted Value by Date and Time

Kushal_Chawda — Thu, 24 Aug 2017 10:39:15 GMT

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)

Re: Firstsorted Value by Date and Time

Kushal_Chawda — Thu, 24 Aug 2017 10:40:17 GMT

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)

Re: Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 10:43:52 GMT

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

Re: Firstsorted Value by Date and Time

Kushal_Chawda — Thu, 24 Aug 2017 10:53:21 GMT

Have you tried my solution?

Re: Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 10:58:43 GMT

No, I want to do it as an expression for now, I don't really want to change the script right now.

I have some success with the following expression

AGGR(FirstSortedValue([Transaction Type], Time#(StartTime, 'hh:mm:ss')),Date)

So I would like to SUM the Amount where the transaction type = R

Re: Firstsorted Value by Date and Time

Kushal_Chawda — Thu, 24 Aug 2017 11:05:15 GMT

try this

FirstSortedValue(DISTINCT {<[Transaction Type]={'R'}>} Amount , aggr(Time#(only({<[Transaction Type]={'R'}>} StartTime), 'hh:mm:ss'),Date))

Re: Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 11:13:26 GMT

I have tried that and it returns nothing, I will keep trying, I know it will be a simple solution in the end.

Re: Firstsorted Value by Date and Time

tresB — Thu, 24 Aug 2017 11:16:11 GMT

Hi,

Did you check my last reply?

Re: Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 11:25:56 GMT

Yes, I ca see it's working in your file, nut not in mine??

Re: Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 11:34:10 GMT

Hi,

It's working now with the following

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

Re: Firstsorted Value by Date and Time

sunny_talwar — Thu, 24 Aug 2017 11:38:39 GMT

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)

Best,

Sunny

Re: Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 11:38:47 GMT

I jumped the gun, still not correct.

Re: Firstsorted Value by Date and Time

rustyfishbones — Thu, 24 Aug 2017 11:43:15 GMT

Thanks Sunny, that worked perfectly!