Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks ...
zero days
I have a doubt about how to show the inventory,
coming to the point
i have a ledger table with me and i want to show Zero inventory days and i'm done with this
but now i to want show particular item should not be considered where that item is never sold in that region/
branch due to not available
lets say for instance item A, item B, item C.
where A is avail in Region1 and B is available in Region1 & 2, C is available in only Region3
Hi,
Can we have a fragment of data and what do you want to see as a result?
Regards,
Andrey
Hi,
Refer my thread, Thanks
and also check this,
ABC Analysis to set and remember classification at runtime
Hope this helps you.
Regards,
Deva
lets say an item is zero for all the days in one region but it is in stock for other regions that item should be considered as item never in stock
below logic is implemented foy zero inventory days
stk_temp:
NoConcatenate
load brcode,
item_code,
dt,
qty
from
(qvd)
where item_code='I69195'
;
stk:
NoConcatenate
LOAD brcode,
item_code,
dt,
qty//,
//rangesum(qty,if(item_code=Peek('Item') AND brcode=Peek('brcode'), Peek('Stock'))) AS Stock
//if(item_code=Previous(item_code) and brcode=Previous(brcode),peek('cum_qty')+qty,qty) as cum_qty
Resident stk_temp
where match(brcode,'002','003') and dt >='01-04-2016' and dt <='30-06-2016'
//Group by brcode,
// item_code,
// date(ceil(dt))
order by brcode,item_code,dt
;
drop Table stk_temp;
stk_temp:
NoConcatenate
load Distinct dt
from
(qvd)
where dt >='01-04-2016' and dt <='30-06-2016'
;
left join(stk_temp)
load Distinct brcode
from
(qvd)
where match(brcode,'002','003') and dt >='01-04-2016' and dt <='30-06-2016'
;
left join(stk_temp)
load Distinct item_code
from
(qvd)
//where item_code='I69195', and dt >='01-04-2016' and dt <='30-06-2016'
where match(item_code,'I69195','I65538','I00197','I00303','I00319','I00157','I00158','I00186','I00187') and dt >='01-04-2016' and dt <='30-06-2016'
;
left join(stk_temp)
load *
Resident stk;
drop Table stk;
stk:
NoConcatenate
load brcode,
item_code,
dt,
sum(qty) as qty
Resident stk_temp
Group by brcode,
item_code,
dt;
drop Table stk_temp;
stock_temp:
NoConcatenate
LOAD brcode,
item_code,
dt,
qty,
if(item_code=Previous(item_code) and brcode=Previous(brcode),peek('cum_qty')+qty,qty) as cum_qty
Resident stk
order by
brcode,
item_code,
dt
;
drop Table stk;
STOCK:
NoConcatenate
load
brcode,
item_code,
date(dt) as dt,
qty,
cum_qty,
if(cum_qty=0,1,0) as zero_stock_flag
Resident stock_temp
order by
brcode,
item_code,
dt
;
drop Table stock_temp;
brcode
003
loaded data for 91 days
item_code brcode zero_days
I00197 003 91
i00303 003 91
i65538 003 91
i69195 003 4
Hi Andrey,
This is my script for Zerodays
and now with in this i should consider item which is Zero inventory all the days in Region A but this is avail in another Region B&C
for the item i loaded is
'I69195'
noof days 91
and for particular item it is showing Zero inventory=4 days
now another couple of items are loaded to check but few among them are zero all 91 days
stk_temp:
NoConcatenate
load brcode,
item_code,
dt,
qty
from
(qvd)
where item_code='I69195'
;
stk:
NoConcatenate
LOAD brcode,
item_code,
dt,
qty//,
//rangesum(qty,if(item_code=Peek('Item') AND brcode=Peek('brcode'), Peek('Stock'))) AS Stock
//if(item_code=Previous(item_code) and brcode=Previous(brcode),peek('cum_qty')+qty,qty) as cum_qty
Resident stk_temp
where match(brcode,'002','003') and dt >='01-04-2016' and dt <='30-06-2016'
//Group by brcode,
// item_code,
// date(ceil(dt))
order by brcode,item_code,dt
;
drop Table stk_temp;
stk_temp:
NoConcatenate
load Distinct dt
from
(qvd)
where dt >='01-04-2016' and dt <='30-06-2016'
;
left join(stk_temp)
load Distinct brcode
from
(qvd)
where match(brcode,'002','003') and dt >='01-04-2016' and dt <='30-06-2016'
;
left join(stk_temp)
load Distinct item_code
from
(qvd)
//where item_code='I69195', and dt >='01-04-2016' and dt <='30-06-2016'
where match(item_code,'I69195','I65538','I00197','I00303','I00319','I00157','I00158','I00186','I00187') and dt >='01-04-2016' and dt <='30-06-2016'
;
left join(stk_temp)
load *
Resident stk;
drop Table stk;
stk:
NoConcatenate
load brcode,
item_code,
dt,
sum(qty) as qty
Resident stk_temp
Group by brcode,
item_code,
dt;
drop Table stk_temp;
stock_temp:
NoConcatenate
LOAD brcode,
item_code,
dt,
qty,
if(item_code=Previous(item_code) and brcode=Previous(brcode),peek('cum_qty')+qty,qty) as cum_qty
Resident stk
order by
brcode,
item_code,
dt
;
drop Table stk;
STOCK:
NoConcatenate
load
brcode,
item_code,
date(dt) as dt,
qty,
cum_qty,
if(cum_qty=0,1,0) as zero_stock_flag
Resident stock_temp
order by
brcode,
item_code,
dt
;
drop Table stock_temp;
store STOCK into 'D:\Sai\Ceo_Dashboard\Ceo_transformed_qvd\STOCK.qvd'