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: 
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
Luminary Alumni
Luminary Alumni

LOAD

BuyerNumber,

Amount,

min(LastDayToPay) as Date

Resident YourTable

Group by BuyerNumber, Amount;

jonathandienst
Partner - Champion III
Partner - Champion III

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
Luminary Alumni
Luminary Alumni

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

jonathandienst
Partner - Champion III
Partner - Champion III

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