topic Re: FirstSortedValue() in QlikView

FirstSortedValue()

Volker — Wed, 25 Nov 2020 16:16:04 GMT

I have two Tables:

Table 1 (Tax):

ID
DateFrom
Percent

Table 2 (Invoice):

InvNr
InvDate
Amount
TaxID

The Problem I have is, that I need the Percent from Table 1 where table1.ID=table2.TaxID and the last Date where table1.DateFrom<=table2.InvDate

In SQL I would describe it as:

select
table1.Percent
from table1, table2
where table1.ID=table2.TaxID and table1.DateFrom<=table2.InvDate
order by table1.DateFrom desc Limit 1

but I have no idea how to say it in QlikView.

Re: FirstSortedValue()

fosuzuki — Tue, 09 Jun 2020 08:19:02 GMT

You can use IntervalMatch() function. More info here.

Example:

IntervalTable: LOAD Key, ValidFrom, Team from IntervalTable.xls; NullAsValue FirstDate,LastDate; Key: LOAD Key, ValidFrom as FirstDate, date(if(Key=previous(Key), previous(ValidFrom) - 1)) as LastDate, Team RESIDENT IntervalTable order by Key, ValidFrom desc; drop table IntervalTable; Transact: LOAD Key, Name, Date, Sales from Transact.xls; INNER JOIN intervalmatch (Date,Key) LOAD FirstDate, LastDate, Key RESIDENT Key;

Re: FirstSortedValue()

Brett_Bleess — Wed, 01 Jul 2020 19:29:39 GMT

If the other partners suggested worked, we would appreciate it if you would close out the thread by using the Accept as Solution button on that post to mark things. This will give them credit for the assistance and let the other Community Members know it helped. If you did something different, you can post that and then mark that post, and if you still require assistance, please leave an update post.

You can also search the Design Blog area for further potential ideas too:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett