How far did I travel to pick some goods?

[soton34]

Dear All

I've tried asking this question already but I managed to confuse even myself!

Basic Idea

We have a warehouse full of stock that occasionally gets moved around.

We pick from Sales Orders and I would like to know how to calculate how far we walked to pick a Sales Order.

Tables

Stock Location History

Batch Number	Location	Distance	Date
1	A035	6	01/01/12
2	B055	9	01/01/12
3	A099	7	01/01/12
4	C087	15	01/01/12
1	D014	3	01/04/12
1	E056	2	01/05/12

Sales History:

Sales Order	Batch Number	PickDate
123456	1	01/02/12
654321	1	15/04/12
234432	1	12/05/12
234432	2	12/05/12
234432	3	12/05/12

Description

So you can see from the Stock History Table that the Batch Number 1 was in A035 on 01/01/12, we moved it to D014 on 01/04/12 and again to E056 on 01/05/12

So Sales Order 123456 was picked from location A035 and Sales Order 654321 was picked from D014

Required Result

I want a chart that lists the Sales Orders and advises the sum of the distance (total) walked:

Sales Order	Total Distance
123456	6
654321	3
234432	18( 2+9+7)

Formulas

So I need a formula that I can put in a chart to select the Distance based on the PickDate.

Eg SUM(IF(PickDate>=Date,Distance))

Problem

Obviously the above doesn't work because it is then adding all the previous Distance values together.

Any bright ideas?

[swuehl]

Your distance per batch number is a slowly changing dimension, so you can apply any solution appropriate to a slowly changing dimension like an INTERVALMATCH LOAD prefix.

First you need to create a time interval with valid from / start date and valid to / end date from your Date field.

This might help you in creating these fields:

http://community.qlik.com/message/169873#169873

There are a lot of examples here in the forum covering INTERVALMATCH, search for INTERVALMATCH or currency exchange rate, since handling currencies is a often needed feature (and very similar to your problem, replace distance with exchange rate and calculate a price instead of a distance)

Then you find something like

http://community.qlik.com/message/93746#93746

Hope this helps,

Stefan

edit:

Found some time, attached a possible approach to your problem

INPUT:

LOAD [Batch Number],

     Location,

     Distance,

     Date

FROM

[http://community.qlik.com/thread/57568?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

Intervals:

LOAD *, if(previous([Batch Number]) = [Batch Number],date(peek(Date)-1), today()) as ToDate Resident INPUT order by [Batch Number], Date desc;

drop table INPUT;

Orders:

LOAD [Sales Order],

     [Batch Number],

     PickDate

FROM

[http://community.qlik.com/thread/57568?tstart=0]

(html, codepage is 1252, embedded labels, table is @2);

join IntervalMatch (PickDate, [Batch Number]) LOAD Date, ToDate, [Batch Number] Resident Intervals;

In the front end, just create a table chart with dimension batch number and expression sum(Distance)

[soton34]

Thanks Stefan

Looks helpful

I have a bit of work to do as I have over simplified the tables for the example above but I will let you know how I get on....

Edit:

Here is my script, clunky as it is.

FifoLocationHistory:
ODBC CONNECT TO [AccessClient]
 LOAD depot as FIFODepot, 
 `stock_code`  as FIFOStockCode, 
 `stock_code` & '.' & `batch_ref` as FIFOBatch, 
 `bin_location` as FIFOLocation, 
 date(`date_imported`,'DD/MM/YY') as FromDate;
SQL SELECT *
FROM `local_fifo_history`;
 

outer join LOAD 
 Location as FIFOLocation, 
 [Distance from Bench] as FIFOLocDist,
 [Rack No] as FIFOLockRack
// LocRow,
// LocUpDown
FROM Spreadsheet.xlsx (ooxml, embedded labels, table is Sheet1);
 

Intervals:
LOAD *, if(previous(FIFOBatch) = FIFOBatch,date(peek(FromDate)-1), today()) as ToDate Resident FifoLocationHistory order by FIFOBatch, FromDate desc;

The problem I have initially is that there are duplications in the Intervals table and so some lines are getting counted twice when I sum(FIFOLocDist)

It gets worse when I add the 'drop FifoLocationHistory' instruction

Any thoughts?

[soton34]

Dear Stefan

I have continued to work on this as I smell that I am getting closer.

Apologies for the fragmented data sources but this is the only way that I can get all of the disparate information.

My simulation script is now:

Invoices:
LOAD Invoice as InvoiceNo, 
 INVOICEDATE
FROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is Invoices);
DSPandDINMoves:
// Batch information is stored separately and is not always linked to the InvoiceNo directly
// So this gets one type of movement (DSP)
LOAD [Trans Type] as FIFOMove, 
 [Trans Date] as FIFOMoveDate, 
 [Prod Code] &'.'& [Batch No] as FIFOBatch, 
 Reference as FIFOAdvice
FROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is mio19)
where [Trans Type]='DSP';
//This links the InvoiceNo to the DSP Movement
inner join LOAD [ORDER NUMBER] as FIFOAdvice, 
 [INVOICE NUMBER] as FIFOInvoice,
 [INVOICE NUMBER] as InvoiceNo
FROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is SOIO);
//This joins in the InvoiceNo from the DIN Movement
Join LOAD [Trans Type] as FIFOMove, 
 [Trans Date] as FIFOMoveDate, 
 [Prod Code] &'.'& [Batch No] as FIFOBatch, 
 Reference as FIFOInvoice,
 Reference as InvoiceNo
FROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is mio19)
where [Trans Type] = 'DIN';
//So I now have a table DSPandDINMoves that contains the batch information for the invoices
// I don't need FIFOAdvice so...
drop field FIFOAdvice;
//Next I need the history of where each batch is located on any given date
fifo_history:
LOAD stock_code &'.'& batch_ref as FIFOBatch, 
 bin_location as FIFOBin, 
 date_imported as FromDate
FROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is local_fifo_history);
//This joins in the distance
inner join LOAD Location as FIFOBin, 
 [Distance from Bench] as Distance
FROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is Locations);
//Now using the code from post about time intervals
FIFOHistory:
LOAD *, if(previous(FIFOBatch) = FIFOBatch,date(peek(FromDate)-1), today()) as ToDate 
Resident fifo_history 
order by FIFOBatch, FromDate desc;
//fifo_history no longer required so
 drop table fifo_history;
//Now to join it all together using INTERVALMATCH
INNER JOIN INTERVALMATCH (FIFOMoveDate, FIFOBatch) 
LOAD
          FromDate,
          ToDate,
          FIFOBatch
resident FIFOHistory;

Two simple date variables give me the opportunity to identify the Invoice(s) in question

But for some reason when i use the expression SUM(IF(INVOICEDATE>=INVFrom and INVOICEDATE<=INVTo,Distance)) it sometimes doubles the distance

Please see the attached sample

Like I said, so close I can smell it, but as yet not quite.

Can you help?

[swuehl · Accepted solution]

In your DSPandDINMoves table, I think you duplicated records for the Invoice date, June 1st (Invoice No = 2).

I assume that's due to your JOIN, maybe check the values of FIFOADVICE on non-matching values (and consider using maybe a LEFT JOIN).

[soton34]

Stefan

Very many thanks for your assistance and persistance.

Your presumption was correct, it was loading the invoice 2 twice (it was a simple mistake when I created the test tables)

To confirm the actions that worked - in case anyone else ever picks up this discussion:

Create a table containing the movement information to be matched (in this case it was an Invoice Table)

Create a table containing the information on certain dates (Can be Currency or Location or any other changing value)

Use Peek to create From and To dates

Use INTERVALMATCH to link the two tables (Dates and Movements)

The working file is attached

Again Stefan, many thanks, I couldn't have done it without you.

Sam