topic Re: Problem with dates in QlikView

Problem with dates

Fri, 26 Feb 2016 13:00:34 GMT

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

Re: Problem with dates

sunny_talwar — Fri, 26 Feb 2016 17:16:46 GMT

Try Max() function with POLine as your dimension:

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

Re: Problem with dates

Chanty4u — Fri, 26 Feb 2016 17:22:50 GMT

Max(Urfield)

=Max([Delivery Date])

Re: Problem with dates

Mon, 29 Feb 2016 07:42:51 GMT

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

Re: Problem with dates

Anonymous — Mon, 29 Feb 2016 08:03:44 GMT

try like this?

TableA:

Load

POLIne,

CreatedOnDate.

DelieveryDate

From TableA;

Load

POLine,

lastvalue(DelieveryDate) as DelieveryDate

Resident TableA

Group By POLine;

Re: Problem with dates

florentina_doga — Mon, 29 Feb 2016 08:28:35 GMT

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;

Re: Problem with dates

jagan — Mon, 29 Feb 2016 08:31:23 GMT

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.

Re: Problem with dates

Mon, 29 Feb 2016 09:05:17 GMT

Hello Jagan,

it says:

Invalid expression

MaxDate:

LOAD

POLine,

Max(CreatedOnDate) AS MaxCreationDate,

1 AS MaxDeliveryDateFlag

RESIDENT Data

Group By CreatedOnDate

Kind regards

Philipp

Re: Problem with dates

Anonymous — Mon, 29 Feb 2016 09:14:55 GMT

BY USING THE MAX FUNCTION TO FIND OUT THE TPUT

Re: Problem with dates

jagan — Tue, 01 Mar 2016 01:15:48 GMT

Hi,

Sorry I missed POLine in Group By

MaxDate:

LOAD

POLine,

Max(CreatedOnDate) AS MaxCreationDate,

1 AS MaxDeliveryDateFlag

RESIDENT Data

Group By POLine, CreatedOnDate;

Re: Problem with dates

MayilVahanan — Tue, 01 Mar 2016 04:32:05 GMT

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

Re: Problem with dates

jonathandienst — Tue, 01 Mar 2016 04:57:00 GMT

Use FirstSortedValue:

...

FirstSortedValue(DeliveryDate, -CreatedOnDate) As LatestDelivery,

...

Re: Problem with dates

Digvijay_Singh — Tue, 01 Mar 2016 05:15:18 GMT

Like this -