topic Re: Use Peek/Previous from 2 tables in QlikView

Use Peek/Previous from 2 tables

markgraham123 — Fri, 16 Feb 2018 03:45:57 GMT

Hi All -

I have 2 diffferent tables which i cannot join into one table due to my data structure.

Table1
Item	Units
A	10
B	20
C	30
D	40
E	50

Table2
OrderDate	Order	Item	Req
2/14/2018	ABC	A	10
2/14/2018	ABC	B	20
2/15/2018	DEF	A	10
2/15/2018	GHI	C	30

Req O/P:
OrderDate	Order	Item	Req	Flag
2/14/2018	ABC	A	10	Y
2/14/2018	ABC	B	20	Y
2/15/2018	DEF	A	10	N
2/15/2018	GHI	C	30	Y

I wanted to start allocating my units by oldest order date first.

Once allocated, i wanna subtract the allocated units from units it doesnot get allocated in a duplicate way.

Once the units cannot fill 'req' field, 'N' will populate, else 'Y'

Can someone please help??

Please note that i just want to lookup the value from Table1, but not join it.

Wanted to do this in script.

Any help is highly appreciated.

Re: Use Peek/Previous from 2 tables

sunny_talwar — Fri, 16 Feb 2018 10:51:42 GMT

So, if the data was something like this

Table2
OrderDate	Order	Item	Req
2/14/2018	ABC	A	5
2/14/2018	ABC	B	20
2/15/2018	DEF	A	5
2/15/2018	GHI	C	30

The output would have been this?

OrderDate	Order	Item	Req	Flag
2/14/2018	ABC	A	10	Y
2/14/2018	ABC	B	20	Y
2/15/2018	DEF	A	10	Y
2/15/2018	GHI	C	30	Y

Also, what would be the output for a scenario like this

Table2
OrderDate	Order	Item	Req
2/14/2018	ABC	A	5
2/14/2018	ABC	B	20
2/15/2018	DEF	A	8
2/15/2018	GHI	C	30

Re: Use Peek/Previous from 2 tables

vikraant — Fri, 16 Feb 2018 10:58:43 GMT

Hi Mark,

As far as I have understood the problem, what is required is, if total number of units of item in table 2 exceeds that mentioned in table 1 then flag should be 'N' . If the number of units of item in table 2 is less than that in table 1 , then the flag should be 'Y'. And this should be sorted as per the date.

Try out the following logic :

//------------------------------------------------------------------------------------------------------------------------------

//Assuming one to one mapping for units

Table1 :

Mapping Load * Inline

[

Item,	Units
A,	10
B,	20
C,	30
D,	40
E,	50

];

[Req O/P]:

Load

OrderDate,

Order,

Item,

Req ,

if( Item = Peek(Item) , Peek(Req_Cumm) + Req , Req ) as Req_Cumm,

if( Req_Cumm > ApplyMap('Table1', Item, 0), 'N', 'Y') as Flag

Resident Table2 Order By OrderDate, Order, Item ;

Drop Table Table2;

//------------------------------------------------------------------------------------------------------------------------------

Best Regards,

Vikraant

Re: Use Peek/Previous from 2 tables

Peter_Cammaert — Fri, 16 Feb 2018 11:05:01 GMT

Order Fulfillment?

Joining your stock to those Order rows is pretty much the easiest way to get to your end-result. Keep in mind that in the end such a solution wouldn't force you to keep any superfluous fields in your order table. Just the Flag field.

As possible methodology:

Create a mapping table that maps each product ot its stock level.
Take your orders table, and order it by product and date. Use an ORDER BY clause when performing the next two steps. ORDER BY happens before anything else.
At the same time, whenever the previous product is different from the current one, lookup the initial stock value using the mapping table and set Flag=Y if you have enough stock and Flag=N if the initial stock cannot fulfill the requested amount. Store the remaining stock in a new column.
If the current product is the same as the previous product, compare the remaining stock of the previous row to the ordered amount. Set Flag=Y if you still have enough stock and Flag=N if the previous stock cannot fulfill the requested amount. Store the remaining stock in a new column.

Now remove the Remaining stock column and reorder your table as you like. IS that possible in your data model?

Peter

Re: Use Peek/Previous from 2 tables

markgraham123 — Fri, 16 Feb 2018 14:45:21 GMT

Your output example is correct.

For the scenario you have provided, output will be as follows:

Table2
OrderDate	Order	Item	Req Flag
2/14/2018	ABC	A	5 Y
2/14/2018	ABC	B	20 Y
2/15/2018	DEF	A	8 N
2/15/2018	GHI	C	30 Y

Re: Use Peek/Previous from 2 tables

Peter_Cammaert — Fri, 16 Feb 2018 15:26:54 GMT

Another interesting variation on Sunny's example. What would be the Flag value for the last line:

Table2
OrderDate	Order	Item	Req
2/14/2018	ABC	A	5
2/14/2018	ABC	B	20
2/15/2018	DEF	A	8
2/15/2018	GHI	C	30
2/16/2018	XYZ	A	3

Is the Req=8 line taking those last 5 pieces (partial delivery)?

Re: Use Peek/Previous from 2 tables

markgraham123 — Fri, 16 Feb 2018 15:33:34 GMT

Yes sir.

As it now has only 5 units left after allocating units to first order, it says N for another order.

Re: Use Peek/Previous from 2 tables

Peter_Cammaert — Fri, 16 Feb 2018 15:54:38 GMT

Like this?

(I'm posting this at the end, because this example does what the previous post tries to explain)