Multiple Ships and Returns - Unwanted Combinations

[johnca]

I have a dilemma I'm not able to fix. The data I have is in separate ship and return tables, and any serial number can be shipped multiple times and returned multiple times. What I'm ending up with in my tables are every possible combination of ship and return, but need to eliminate the phantom ones and retain only the real ones. The data is pulled from an Oracle DB, sorted by serial number then date ascending, then saved into separate qvd's for use in multiple dashboards.

This is inline code I used to simulate one serial number possibility, but of course there are many serial numbers:

SampleShip:

Load * Inline [SerNo,ShipDate

103828,38560

103828,38803

103828,39037

103828,39127];

SampleReturn:

Load * Inline [SerNo,ReturnDate

103828,38778

103828,38958

103828,39087];

So, for this perticular serial number this is how my table ends up when I use the following expression:

Difference =

if(ReturnDate<>Null(),Num(if(ReturnDate-ShipDate>0, ((ReturnDate-ShipDate)/Count(SerNo)/30.42)),'#,###.0'), Num(((Today()-ShipDate)/30.42),'#,###.0'))

SerNo	ShipDate	ReturnDate	Difference
103828	38560	38778	7.2	good
103828	38560	38958	13.1	bad
103828	38560	39037	17.3	bad
103828	38803	38958	5.1	good
103828	38803	39087	9.3	bad
103828	39037	39087	1.6	good

Here is what I need it to look like:

SerNo	ShipDate	ReturnDate	Difference
103828	38560	38778	7.2
103828	38803	38958	5.1
103828	39037	39087	1.6
103828	39127		-

Since the last ship date (39127) does not have a corresponding return date it doesn't show in the table, but I will ultimatelty need to add this row and include today's date as the Return Date. Assuming today = 41180 (9/28/2012) It would look like this:

SerNo	ShipDate	ReturnDate or CurrentDate	Difference
103828	38560	38778	7.2
103828	38803	38958	5.1
103828	39037	39087	1.6
103828	39127	41180	67.5

I've tried adding a counter ("-1", "-2", "-3", etc) to each serial number and linking with that, but it breaks if I am missing a shipment or return record, and this is the case over and over so not an option.

Have any of you gone down this path before and come up with a solution? I have yet to find one. Appreciate any solutions that will get me close than I am.  --john

[swuehl]

Henric will probably kill me because I am using a join here:

SampleShip:

Load *,AutoNumber(ShipDate,SerNo&'Sam') as ShipID Inline [

SerNo,ShipDate

103828,38560

103828,38803

103828,39037

103828,39127];

SampleReturn:

Left join Load *, autonumber(ReturnDate,SerNo&'Ret') as ShipID Inline [

SerNo,ReturnDate

103828,38778

103828,38958

103828,39087];

> "I've tried adding a counter ("-1", "-2", "-3", etc) to each serial number and linking with that, but it breaks if I am missing a shipment or return record, and this is the case over and over so not an option."

Sorry, I think I haven't fully understood this part. You are missing some records? How do you link ShipDates and ReturnDates then?

[johnca]

That is similar to adding a counter to each record. If I have all the records it works…if I’m missing a record it doesn’t. For instance, try removing the first SampleShip record “103828,38560” and see what happens. You get a return record earlier than the ship record.

The counter I refer to is adding a “-1”, “-2”, “-3”, etc for each serial number’s ship record using the following: (assuming the data has already been sorted by serial number then date)

if(Previous(ShipDate) <> ShipDate, 1, peek(Counter) + 1) as Counter,*;

So the new field Counter data would look like 103828-1, 103828-2, 103828-3, etc. Doing the same with return records allows me to join on that field, but if I’m missing any record it breaks.

I did learn something though…thanks!

[swuehl · Accepted solution]

Ok, I think I understand now.

Well, if you are coping with missing records, but still want to calculate something, I think you need to set up some rules and make some assumptions.

An assumption I've made: If you are ordering your shipment / return dates by date for a given SerNo, there are no 2 subsequent records missing.

For example:

SerNo, Date, Type

1, 30200, Ship

1, 30201, Return

1, 30202, Ship

1, 30203, Return

If you remove the second and third record, you get a valid combinatin of Shipment and Return, but your calculation of the date difference is quite off, right?

Since you don't know which records are missing, all you can do is hoping that your assumption is mostly correct.

Then you can set up some rules, how to handle e.g.

SerNo, Date, Type

1, 30200, Ship

1, 30201, Return

1, 30203, Return

In this case, I assume you want to remove the second return record from your calculation.

SerNo, Date, Type

1, 30200, Ship

1, 30201, Ship

1, 30202, Return

1, 30203, Ship

Here I assume that you want to keep all Ship records and link the Return to the second Shipment. The first and third Shipment will have no Return record, and you can assign e.g. today() in your chart as Return if needed (as I did in my last post).

You can create a script that handles these rules like:

Sample:

Load SerNo, 'Ship' as Type, ShipDate as Date  Inline [

SerNo,ShipDate

103828,38560

103828,38803

103828,39037

103828,39127

103829,39127

];

Load SerNo, 'Return' as Type, ReturnDate as Date  Inline [

SerNo,ReturnDate

103828,38778

103828,38958

103828,39087

103829,39128];

TMP:

LOAD *,

if(Type ='Ship', autonumber(recno(),SerNo), if(peek(Type)='Ship' and peek(SerNo) = SerNo, peek(ID))) as ID

Resident Sample order by SerNo, Date;

Drop table Sample;

RESULT:

LOAD SerNo, Date as ShipDate, ID Resident TMP where Type = 'Ship';

left join LOAD SerNo, Date as ReturnDate, ID Resident TMP where Type = 'Return';

drop table TMP;

The trick is creating a table with all records for a SerNo ordered by Date, and then create an ID based on the rules. Then you can either just use this TMP table or create again a table with split up fields for ShipDate and ReturnDate (RESULT).

Hope this helps,

Stefan

[johnca]

Stefan, I am astonished the answer was possible at all. I applied this to a much larger dataset and it works flawlessly, without adding unwanted combinations and actually knowing which return record belonged to which shipment. Amazing! The only unfortunate part is that it takes quite a while to run on my larger dataset, so I'll need to play with incremental loading. Again, much thanks!

--john

[swuehl]

You are welcome.

If the join in my above sample costs to much time in your real setting, consider replacing it with a mapping table:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

You just need to create a unique key out of SerNo and ID to make this work.

[johnca]

I'm somewhat familiar with the Mapping function, and have seen it used in a similar manner but wasn't sure how it did its thing. I'm used to simple maps so this will be a challenge, but not impossible. Again, much gratitude.