1 Reply Latest reply: May 6, 2017 1:17 AM by kakani sai RSS

    Stock ledger table loading issue

    kakani sai

      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 [D:\Sai\eco_032000_STOCKLED_2016.qvd]
      (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
      [D:\Sai\eco_032000_STOCKLED_2016.qvd]
      (qvd)
      where dt >='01-04-2016' and dt <='30-06-2016'
      ;

      left join(stk_temp)
      load Distinct brcode
      from [D:\Sai\eco_032000_STOCKLED_2016.qvd]
      (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 [D:\Sai\eco_032000_STOCKLED_2016.qvd]
      (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