Re: showing rows that are 'out of date' - Qlik Community

Report Inappropriate Content · ‎2012-09-19

Hi everybody,

I have the following problem:

The table is as following:

Client Package# Status Delivery Date

1 1 Sent 01-05-2012

1 1 Delivered 02-05-2012

1 1 Sent 10-05-2012

1 4 Sent 10-05-2012

2 5 Sent 20-06-2012

2 5 Delivered 30-06-2012

3 1 Sent 05-08-2012

3 1 Sent 15-09-2012

3 1 Delivered 17-09-2012

Here i want to show only the Rows where a 'Package#' of a 'Client' has been 'Sent' but not been 'Delivered' within 7 days or not been delivered at all.

The package number can be reused in the future.

The result must show the following:

Client Package# Status Delivery Date

1 1 Sent 10-05-2012

1 4 Sent 10-05-2012

2 5 Sent 20-06-2012

3 1 Sent 05-08-2012

I was told that this could be achieved within the script with a mapping load, but i would not know how.

Any help is appreciated.

Thanks,

SY

Report Inappropriate Content · ‎2012-09-19

Hi,

First of all, you should avoid to re-use the package#. Would this be possible? Furthermore, does a date in the column Delivery Date mean sent date if status is Sent, or is this expected delivery date if status is Sent? This will not be easy to solve if you don't change methodology in order to generate unique keys.

Brgds,

sebablum

chematos · ‎2012-09-19

Client Package# Status Delivery Date Delivered Date

1 1 Sent 01-05-2012 02-05-2012

1 1 Delivered 02-05-2012 02-05-2012

1 1 Sent 10-05-2012 02-05-2012

1 4 Sent 10-05-2012 -

2 5 Sent 20-06-2012 30-06-2012

2 5 Delivered 30-06-2012 30-06-2012

3 1 Sent 05-08-2012 17-09-2012

3 1 Sent 15-09-2012 17-09-2012

3 1 Delivered 17-09-2012 17-09-2012

I would create this structure and grouping by Client, Package#, if Delivered Date is greater than Delivery Date, i would create a Flag in another column to know wich has been delivered and wich one doesn´t, all this in the load script

if you have problems to make the code, tell me.

The first structure is with a left join by Client, Package# and you can make the calculations to create the Flag with a resident load of this previous table.

Regards

swuehl · ‎2012-09-19

How do you distinguish between the two packages sent for client 3?

3 1 Sent 05-08-2012

3 1 Sent 15-09-2012

3 1 Delivered 17-09-2012

You expect following outcome:

3 1 Sent 05-08-2012

So I assume that the package sent on 15-09-2012 has been delivered. But how do you know?

What if the package sent on 05-08 has been delivered? That means you would expect to see both lines

3 1 Sent 05-08-2012

3 1 Sent 15-09-2012

right? First the one that has been delivered, but not within the allowed period of days, second the one that has not been delivered yet.

IMHO, your problem seems to not provide full information to retrieve an unambiguous answer. Do you have any more information in your table that could help us here?

Report Inappropriate Content · ‎2012-09-20

Hi,

@sebablum : the delivery date is the date of the status. When a package has been 'Sent' it gets a date. Same for the status 'Delivered' when the package is delivered it gets a date.

@José Mª Tos : I will let you know if this has worked for me.

@swuehl : There are more information in the table but none that will make the row unique.

The answer i got from the client when i explained this was : the date that is nearest to the delivery date of the 'Package' that is 'delivered' is the package that has been 'sent'.

So if you got two of the same packages number with the status sent date on 20-02-2012 and the other 21-02-2012 and the delivery date of status delivered = 22-02-2012, then the package with the status 'Sent' on 21-02-2012 is the one that has been delivered.

swuehl · ‎2012-09-20

Then you can try maybe like this:

SET DateFormat='DD-MM-YYYY';

INPUT:

LOAD *, Client&'-'&[Package#] as ClientPack INLINE [

Client, Package#, Status, Delivery Date

1, 1 , Sent , 01-05-2012

1, 1 , Delivered , 02-05-2012

1, 1 , Sent , 10-05-2012

1, 4 , Sent , 10-05-2012

2, 5 , Sent , 20-06-2012

2, 5 , Delivered , 30-06-2012

3, 1 , Sent , 05-08-2012

3, 1 , Sent , 15-09-2012

3, 1 , Delivered , 17-09-2012

];

Result:

LOAD *,

if(not DeliveredFlag or DeliveryDuration >7, 1) as NotDeliveredOrLate;

LOAD *,

if (Status = 'Sent',if(peek(Status)='Delivered' and peek(ClientPack)= ClientPack,1,0)) as DeliveredFlag,

if(Status = 'Sent' and peek(Status)='Delivered' and peek(ClientPack)= ClientPack, peek([Delivery Date])-[Delivery Date] ) as DeliveryDuration

resident INPUT order by Client, [Package#], [Delivery Date] desc;

drop table INPUT;

Hope this helps,

Stefan