Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Getting the "oldest" Value

Hello again,

Another question from me again .

I have a table with Data:

Buyer number - amount -  Last Day to pay(Date format)

One buyer can have more then one Days to pay. Is there a possibilty to write a script: Give me for every buyer only the oldest Date?

I can import all the data into QV(but its a huge file), but i would like if its possible to import only the oldest Date(with buyer number and amount).

how does the formula/scrpit looks like?

Thank you,

Kristian

4 Replies
jvitantonio
Valued Contributor III

Re: Getting the "oldest" Value

LOAD

BuyerNumber,

Amount,

min(LastDayToPay) as Date

Resident YourTable

Group by BuyerNumber, Amount;

MVP
MVP

Re: Getting the "oldest" Value

Hi

The previous script will give the oldest date ro pay a specific amount. Eg customer A, oldest to pat $150, customer A oldest date to pay $200 etc. Is that the requirement?

I understood the question to mean the oldest date by customer, and the amount payable on that date. If that is so, then

Payable:

LOAD

     BuyerNumber,

     min(LastDayToPay) as LastDate

Resident YourTable

Group by BuyerNumber;

Join (Payable)

LOAD 

     BuyerNumber,

     LastDayToPay as LastDate

     Amount

Resident YourTable;

should do the trick

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jvitantonio
Valued Contributor III

Re: Getting the "oldest" Value

Now that I re-read the post, Jonathan is right. Thanks.

MVP
MVP

Re: Getting the "oldest" Value

In fact the second load would be better as

Join (Payable)

LOAD

     BuyerNumber,

     LastDayToPay as LastDate

     Sum(Amount) As Amount

Resident YourTable

Group By BuyerNumber, LastDayToPay;

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser