Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!!!!!!!
Looks like join + generic clauses in load script should help.
regards
Darek
hi,
could you explain it. I am new to qlikview.
Example1:
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;
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