Stock ledger table loading issue

Fri, 05 May 2017 17:13:15 GMT

Hi Folks one ....

I'm analysing the Stock ledger table contains of around 4000000+ records

my intention is to show Zero inventory days items in all branches 150+

now i was succeeded in implementing the logic for Few records with cumulative, like 10 branches and couple of items and even loaded first 50000 records sucessfully using FIRST 50000 in load script which took 2 hrs of time in our server,

now the issue is with full load.... not able to do that and reload got failed couple of times.

What can be done to achieve this succesfully

Monthend(date) wise i should be considered

below is my script .....

could any one help me to over come this issue ....

stk_temp:
NoConcatenate
load brcode,
    item_code,
    dt,
     qty
from
(qvd)
where item_code='I69195'
;

stk:
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_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') 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,
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 ''

stalwar1‌

Apr 29, 9:40 AM

Re: Stock ledger table loading issue

Sat, 06 May 2017 05:17:55 GMT

Data size what i have mentioned is huge and not like 4000000+ bigger than this

topic Stock ledger table loading issue in GeoAnalytics

Stock ledger table loading issue

Re: Stock ledger table loading issue