Qlik Community

Qlik GeoAnalytics Discussions

Discussion Board for collaboration regarding Qlik GeoAnalytics.

Not applicable

Stock ledger table loading issue

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 ''



Apr 29, 9:40 AM

1 Reply
Not applicable

Re: Stock ledger table loading issue

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

Community Browser