New table is created and I don't know how to avoid it
Hello.
I need to assign an incremental index for any item id in a table and then merge two tables according to this index (called crono in the code below). The idea is enumerating cronologically each sold and bought item in a warehouse, so I can say how much I have paid and sold each single article (assuming a FIFO structure of the warehouse). I would thus know the price of the n-th bought article X and the price of the m-th bought article Y in our warehouse life, for example, and how much I've sold them for.
I'm using the following code:
LET tot_materials = fieldValueCount('materiale'); //total amount of distinct articles LET i = 1; Movement: LOAD rowno() as crono ,material ,sell_price ,year RESIDENT Sales WHERE material = FieldValue('material',1); INNER JOIN LOAD rowno() as crono ,material ,buy_price RESIDENT Purchases WHERE material = FieldValue('material',1);
FOR i=2 TO $(tot_materials) LOAD rowno() as crono ,material ,sell_price ,year RESIDENT Sales WHERE material = FieldValue('material',$(i)); INNER JOIN LOAD rowno() as crono ,material ,buy_price RESIDENT Purchases WHERE material = FieldValue('materials',$(i)); NEXT
DROP TABLES Purchases ,Sales ;
I get the result I want, but with a side effect: tables called Sales-k for each k between 1 and (tot_materials-1) are generated and I can't drop them.
Thank you Miguel for your support. Since I've encounted another issue (out of memory) I had to consider the whole script structure. Now I don't have the problem anymore.
