topic Re: unit of measurement wise stock split in QlikView

unit of measurement wise stock split

Mon, 26 May 2014 09:15:29 GMT

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

Re: unit of measurement wise stock split

Mon, 26 May 2014 09:21:52 GMT

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

regards

Darek

Re: unit of measurement wise stock split

Mon, 26 May 2014 09:50:11 GMT

hi,

could you explain it. I am new to qlikview.

Re: Re: unit of measurement wise stock split

Mon, 26 May 2014 09:59:05 GMT

Example1:

Re: Re: unit of measurement wise stock split

Mon, 26 May 2014 10:34:47 GMT

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;

Re: unit of measurement wise stock split

Mon, 22 Sep 2014 14:05:20 GMT

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;

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