Problem with dates

[#1]

Hello,

assume the following problem.

I have several Purchase Order Lines. Each week I contact the supplier at which date he predicts to deliver.

Unfortunatley the supplier is not very reliable and he changes his confirmed delivery date each week.

So we have a kind of confirmation history. Meaning by the CreatedOnDate I know his latest change.

POLine	CreatedOnDate	DeliveryDate
1	1-Feb-16	10-Feb-16
1	8-Feb-16	15-Feb-16
1	15-Feb-16	13-Mar-16
1	22-Feb-16	30-Mar-16
1	29-Feb-16	12-Mar-16
2	22-Feb-16	25-Mar-16
2	29-Feb-16	26-Mar-16
3	8-Feb-16	1-Mar-16
3	15-Feb-16	5-Mar-16
3	22-Feb-16	3-Mar-16

I would like to see now an analysis what is the most recent Delivery Date, meaning where the CreatedOnDate is at max for the respective PO Line. As output I don't like to see the CreatedOnDate but only the Delivery Date.

POLine	Delivery Date
1	12-Mar-16
2	26-Mar-16
3	3-Mar-16

Do you know how this is possible to build in QV?

Thanks and Kind regards

Philipp

[MayilVahanan]

Hi

Try like this

Data:

LOAD

POLIne,

CreatedOnDate.

DelieveryDate

FROM DataSource;

MaxDeliveryDate:

LOAD

POLIne,

Max(CreatedOnDate) AS MaxCreationDate

RESIDENT Data

Group By POLIne;

INNER JOIN(MaxDeliveryDate)

LOAD

POLIne,

CreatedOnDate AS MaxCreationDate,

DelieveryDate AS MaxDeliveryDate

RESIDENT Data;

In Front end

Use POLine and MaxDeliveryDate field

[jonathandienst]

Use FirstSortedValue:

     ...

     FirstSortedValue(DeliveryDate, -CreatedOnDate) As LatestDelivery,

     ...

[Digvijay_Singh]

Like this -