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

[sunny_talwar]

Try Max() function with POLine as your dimension:

Date(Max([Delivery Date), 'DD-MMM-YYYY)

[Chanty4u]

Max(Urfield)

=Max([Delivery Date])

[#4]

Hello both,

it won't work with Max([Delivery Date]) as you see for example in line 4 there is a later delivery date (30-Mar-16) than the most recent one (12-Mar-16). That's the crux of the whole thing. Somehow the system has to search for the max of the CreatedOnDates but has to put out the related Delivery Date.

Kind regards

Philipp

[Anonymous]

try like this?

TableA:

Load

POLIne,

CreatedOnDate.

DelieveryDate

From TableA;

Load

POLine,

lastvalue(DelieveryDate) as DelieveryDate

Resident TableA

Group By POLine;

[florentina_doga]

try this

aa:

load * inline [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];

left join (aa)

load  POLine, max(CreatedOnDate) as date_max_created resident aa group by POLine;

bb:

load POLine, DeliveryDate

resident aa where CreatedOnDate=date_max_created;

drop table aa;

[jagan]

Hi,

If you want this script, try like below

Data:

LOAD

POLIne,

CreatedOnDate.

DelieveryDate

FROM DataSource;

MaxDate:

LOAD

POLIne,

Max(CreatedOnDate) AS MaxCreationDate,

1 AS MaxDeliveryDateFlag

RESIDENT Data

Group By CreatedOnDate;

INNER JOIN(MaxDate)

LOAD

POLIne,

CreatedOnDate AS MaxCreationDate,

Max(DelieveryDate) AS MaxDeliveryDate

RESIDENT Data

Group By POLIne, CreatedOnDate ;

Now you can use MaxDeliveryDateFlag like below in set analysis

Dimension: POLIne

Expression: Only({<MaxDeliveryDateFlag={1}>} MaxDeliveryDate)

Hope this helps you.

Regards,

Jagan.

[#8]

Hello Jagan,

it says:

Invalid expression

MaxDate:

LOAD

POLine,

Max(CreatedOnDate) AS MaxCreationDate,

1 AS MaxDeliveryDateFlag

RESIDENT Data

Group By CreatedOnDate

Kind regards

Philipp

[Anonymous]

BY USING THE MAX FUNCTION TO FIND OUT THE TPUT

[jagan]

Hi,

Sorry I missed POLine in Group By

MaxDate:

LOAD

POLine,

Max(CreatedOnDate) AS MaxCreationDate,

1 AS MaxDeliveryDateFlag

RESIDENT Data

Group By POLine, CreatedOnDate;