unit of measurement wise stock split

[#1]

Hi

i have a table which has location wise item stock. Another table for its basic unit of measurement split up.

eg:-   table1

item_no    loc_code      curr_qty

abc              A                1000

abc              B                  500

               table 2()

item_no       uom_code(need not be sorted)         uom_unit(need not be sorted)

abc              CTN                                                              20

abc              PCS                                                             10

i have to split up the curr_qty on the basis of uom_code.

format is:-

item_no  loc_code  curr_qty      CTN        PCS

abc           A           1000          1000/20    remainder from first column/10

and it goes on like that. Unit of measurement split can be different for different item also.

someone please help!!!!!!!!!

[#2]

Looks like join + generic clauses in load script should help.

regards

Darek

[#3]

hi,

could you explain it. I am new to qlikview.

[#4]

Example1:

[#5]

Example2:

try this script...

tab1:

LOAD * INLINE [

    item, loc, qty

    abc, A, 100

    abc, B, 500

];

tab_uom:

LOAD * INLINE [

    item, uom_code, uom_unit

    abc, container, 20

    abc, PCS, 10

];

con:

load concat(chr(39)&uom_code&chr(39),',') as tmp Resident tab_uom Group by 1;

let list= peek('tmp');

drop table con;

for Each code in $(list)

join (tab1) load item, uom_unit as $(code) Resident  tab_uom where uom_code='$(code)';

tab2:

load *, qty/$(code) as $(code)_val Resident tab1; 

DROP Field $(code) ;

DROP table tab1;

RENAME Table tab2 to tab1;

NEXT;

[#6]

i am trying to create a table using the concept of the function which was created in oracle. when i am trying to put a loop, the new table with related data is getting duplicate on the basis of no of lines present in loop.

STK_ITM_LOC_CURR:

LOAD

    ITM_DEP_CODE AS T_ITM_DEP_CODE,

     IM_INV_NO_LOC AS T_IM_INV_NO_LOC,

     LOC_CODE_ITM_LOC AS T_LOC_CODE_ITM_LOC,

     CURR_QTY_ITM_LOC AS T_CURR_QTY_ITM_LOC

Resident ITM_LOC;

let NumRows= NoOfRows('STK_ITM_LOC_CURR');

FOR P=0 TO $(NumRows)-1;

  LET vDep_code = Peek('T_ITM_DEP_CODE',$(P),'STK_ITM_LOC_CURR');

  LET vIm_inv_no = Peek('T_IM_INV_NO_LOC',$(P),'STK_ITM_LOC_CURR');

  LET vCurr_qty = Peek('T_CURR_QTY_ITM_LOC',$(P),'STK_ITM_LOC_CURR');

  LET vLoc = Peek('T_LOC_CODE_ITM_LOC',$(P),'STK_ITM_LOC_CURR');

  IF LEN('$vCurr_qty')<>0 AND $(vCurr_qty)<>0 then

       STK_UOM_TAB:

            LOAD UOM_DEP_CODE AS T_UOM_DEP_CODE,

            IM_INV_NO_UOM AS T_IM_INV_NO_UOM,

            UOM_CODE_ITEM AS T_UOM_CODE_ITEM,

            UOM_UNITS_ITEM AS T_UOM_UNITS_ITEM

            Resident ITEM_UOM

            Where UOM_DEP_CODE='$(vDep_code)'

            AND IM_INV_NO_UOM=$(vIm_inv_no)

            Order BY UOM_UNITS_ITEM desc ;

            let NumRows_1=NoOfRows('STK_UOM_TAB');

            FOR Q=0 TO $(NumRows_1)-1;

                 LET vUom_code = peek('T_UOM_CODE_ITEM',$(Q),'STK_UOM_TAB');

                 LET vUom_units = peek('T_UOM_UNITS_ITEM',$(Q),'STK_UOM_TAB');

                 if len('$(vUom_units)')<>0 then

                      if ($(vCurr_qty)>=$(vUom_units))then

                           let vCal= div($(vCurr_qty),$(vUom_units));

                           let vRemainder= Mod($(vCurr_qty),$(vUom_units));

                           FINAL_TAB:

                                LOAD '$(vDep_code)' as FINAL_DEP,

                                          '$(vIm_inv_no)' AS FINAL_IMINVNO,

                                          '$(vLoc)' AS FINAL_LOC,

                                          '$(vCurr_qty)' AS FINAL_CURR_QTY,

                                          '$(vUom_code)' AS FINAL_UOM,

                                           '$(vCal)' AS SAMPLE;

                      ELSE

                           let vCal='$(vCurr_qty)';

                           FINAL_TAB:

                                LOAD '$(vDep_code)' as FINAL_DEP,

                                '$(vIm_inv_no)' AS FINAL_IMINVNO,

                                '$(vLoc)' AS FINAL_LOC,

                                '$(vCurr_qty)' AS FINAL_CURR_QTY,

                                '$(vUom_code)' AS FINAL_UOM,

                                   '$(vCal)' AS SAMPLE ;

                           EXIT for;

                       ENDIF;

              ENDIF;

              if $(vRemainder)<>0 then

                 let vCurr_qty='$(vRemainder)';

            else

                 EXIT For ;

            ENDIF;

           NEXT Q;

  ENDIF;

NEXT P;

here what happens is after entering Q loop, data in final tab is repeated for no of rows in Q loop.

pls help