Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

markgraham123
Contributor II

Use Peek/Previous from 2 tables

Hi All -

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

  

Table1
ItemUnits
A10
B20
C30
D40
E50    

Table2
OrderDateOrderItemReq
2/14/2018ABCA10
2/14/2018ABCB20
2/15/2018DEFA10
2/15/2018GHIC30

     

Req O/P:
OrderDateOrderItemReqFlag
2/14/2018ABCA10Y
2/14/2018ABCB20Y
2/15/2018DEFA10N
2/15/2018GHIC30

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.

7 Replies
MVP
MVP

Re: Use Peek/Previous from 2 tables

So, if the data was something like this

Table2
OrderDateOrderItemReq
2/14/2018ABCA5
2/14/2018ABCB20
2/15/2018DEFA5
2/15/2018GHIC30

The output would have been this?

OrderDateOrderItemReqFlag
2/14/2018ABCA10Y
2/14/2018ABCB20Y
2/15/2018DEFA10Y
2/15/2018GHIC30

Y

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

Table2
OrderDateOrderItemReq
2/14/2018ABCA5
2/14/2018ABCB20
2/15/2018DEFA8
2/15/2018GHIC30
vikraant
Contributor

Re: Use Peek/Previous from 2 tables

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

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

markgraham123
Contributor II

Re: Use Peek/Previous from 2 tables

Your output example is correct.

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

Table2
OrderDateOrderItemReq  Flag
2/14/2018ABCA5         Y
2/14/2018ABCB20       Y
2/15/2018DEFA8         N
2/15/2018GHIC30       Y

Re: Use Peek/Previous from 2 tables

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

Table2
OrderDateOrderItemReq
2/14/2018ABCA5
2/14/2018ABCB20
2/15/2018DEFA8
2/15/2018GHIC30
2/16/2018XYZA3

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

markgraham123
Contributor II

Re: Use Peek/Previous from 2 tables

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

Like this?

Use Peek-Previous from 2 tables thread291651.jpg

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

Community Browser