Skip to main content

# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## How far did I travel to pick some goods?

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 NumberLocationDistanceDate
1A035601/01/12
2B055901/01/12
3A099701/01/12
4C0871501/01/12
1D014301/04/12
1E056201/05/12

Sales History:

Sales OrderBatch NumberPickDate
123456101/02/12
654321115/04/12
234432112/05/12
234432212/05/12
234432312/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 OrderTotal Distance
1234566
6543213
23443218( 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?

1 Solution

Accepted Solutions
MVP

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).

5 Replies
MVP

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)

Contributor III
Author

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,// LocUpDownFROM 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?

Contributor III
Author

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,  INVOICEDATEFROM 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 FIFOAdviceFROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is mio19)where [Trans Type]='DSP';//This links the InvoiceNo to the DSP Movementinner join LOAD [ORDER NUMBER] as FIFOAdvice,  [INVOICE NUMBER] as FIFOInvoice, [INVOICE NUMBER] as InvoiceNoFROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is SOIO);//This joins in the InvoiceNo from the DIN MovementJoin LOAD [Trans Type] as FIFOMove,  [Trans Date] as FIFOMoveDate,  [Prod Code] &'.'& [Batch No] as FIFOBatch,  Reference as FIFOInvoice, Reference as InvoiceNoFROM 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 datefifo_history:LOAD stock_code &'.'& batch_ref as FIFOBatch,  bin_location as FIFOBin,  date_imported as FromDateFROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is local_fifo_history);//This joins in the distanceinner join LOAD Location as FIFOBin,  [Distance from Bench] as DistanceFROM INTERVALMATCH4.xlsx (ooxml, embedded labels, table is Locations);//Now using the code from post about time intervalsFIFOHistory: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 INTERVALMATCHINNER JOIN INTERVALMATCH (FIFOMoveDate, FIFOBatch) LOAD          FromDate,          ToDate,          FIFOBatchresident 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?

MVP

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).

Contributor III
Author

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

Community Browser