topic Re: Stock levels in App Development

Stock levels

mitjap — Sun, 21 Feb 2021 19:11:54 GMT

hello

im searching for solution for days now, tryed with peek, but it i just cant get it to work. its my least app, than i present all i done in my company. This saves my coworker at least 30min in day of his time. Stucked at this stock levels

sifraid	Order	Stock	Sestava	opc_dn
7003 73114 379	11	116,00	2/17/2021 10:17:00 AM	DN202004139
7003 73114 379	5	116,00	2/17/2021 10:46:00 AM	DN202004146
7003 73114 379	2	116,00	2/18/2021 10:53:00 AM	DN202004147
7003 73114 379	7	116,00	2/18/2021 7:35:00 PM	DN202004148

resoult should be

7003 73114 379	11	116,00	2/17/2021 10:17:00 AM	DN202004139
7003 73114 379	5	105,00	2/17/2021 10:46:00 AM	DN202004146
7003 73114 379	2	100,00	2/18/2021 10:53:00 AM	DN202004147
7003 73114 379	7	98,00	2/18/2021 7:35:00 PM	DN202004148

Data is from two excel sheets

LOAD

Num#( sifraid ,'#,##0 ST') as sifraid,

kolicina_skupaj as order,

zalogamrp as stock

opc_dn

FROM....

LOAD

"Delovni nalog" as opc_dn,

Sestava,

FROM....

Thanks for help.

Re: Stock levels

Taoufiq_Zarra — Mon, 22 Feb 2021 08:59:07 GMT

@mitjap Maye be like :

Data: LOAD sifraid, "Order", Stock, Timestamp#(Sestava, 'M/D/YYYY hh:mm:ss TT') as Sestava, opc_dn INLINE [ sifraid, Order, Stock, Sestava, opc_dn 7003 73114 379, 11, 116, 2/17/2021 10:17:00 AM, DN202004139 7003 73114 379, 5, 116, 2/17/2021 10:46:00 AM, DN202004146 7003 73114 379, 2, 116, 2/18/2021 10:53:00 AM, DN202004147 7003 73114 379, 7, 116, 2/18/2021 7:35:00 PM, DN202004148 ]; Tmp: noconcatenate load sifraid, "Order",if(peek(sifraid)=sifraid,peek(Stock)-peek("Order"),Stock) as Stock, Sestava, opc_dn; load * resident Data order by sifraid,Sestava; drop table Data;

output:

Re: Stock levels

mitjap — Mon, 22 Feb 2021 15:37:13 GMT

it works great and as needed, thank you. much appreciated Taoufiq

lot of learning to do, from my side.

but when i try it to integrate in my script i'm falling to do so. i did some scripting in past apps i did, but nothing like this yet.

i wrote it like this in main,i think i do something wrong.

LOAD
Sestava,
"Delovni nalog" as opc_dn
FROM [lib://DataFiles/izvoz voyeger oskrba vstop dn vse linije.xlsx]
(ooxml, embedded labels, table is zxC0A6);

LOAD
Num#( sifraid ,'#,##0 ST') as sifraid,
kolicina_skupaj as "Order",
zalogamrp as Stock,
opc_dn

FROM [lib://DataFiles/planprodajeadria.xls]
(biff, embedded labels, table is planprodajeadria$);

Data:
LOAD sifraid, "Order", Stock, Timestamp#(Sestava, 'M/D/YYYY hh:mm:ss TT') as Sestava, opc_dn INLINE [
sifraid, Order, Stock, Sestava, opc_dn

];

Tmp:
noconcatenate

load sifraid, "Order",if(peek(sifraid)=sifraid,peek(Stock)-peek("Order"),Stock) as Stock, Sestava, opc_dn;
load * resident Data order by sifraid,Sestava;

drop table Data;

Re: Stock levels

Taoufiq_Zarra — Mon, 22 Feb 2021 19:28:37 GMT

@mitjap Maye be like :

Data: LOAD Sestava, "Delovni nalog" as opc_dn FROM [lib://DataFiles/izvoz voyeger oskrba vstop dn vse linije.xlsx] (ooxml, embedded labels, table is zxC0A6); join // or left join or right join LOAD Num#( sifraid ,'#,##0 ST') as sifraid, kolicina_skupaj as "Order", zalogamrp as Stock, opc_dn FROM [lib://DataFiles/planprodajeadria.xls] (biff, embedded labels, table is planprodajeadria$); output: noconcatenate load sifraid, "Order",if(peek(sifraid)=sifraid,peek(Stock)-peek("Order"),Stock) as Stock, Sestava, opc_dn; load * resident Data order by sifraid,Sestava; drop table Data;

Re: Stock levels

mitjap — Mon, 22 Feb 2021 19:57:22 GMT

I should buy you a beer for this. Its working. Thank you kind man.

now its my job to put this solution in good practise.

Re: Stock levels

Taoufiq_Zarra — Mon, 22 Feb 2021 19:52:05 GMT

😁😁😁

Re: Stock levels

Taoufiq_Zarra — Tue, 23 Feb 2021 11:45:43 GMT

@mitjap

where's the error, can you elaborate ?

Re: Stock levels

mitjap — Wed, 24 Feb 2021 10:30:33 GMT

I would like to inform you, that everything is OK.

I run a lot of test and solution is running great.

The problem I see now is in my excel files.

As stated, I was testing a lot, everything is great, just when I go with full data something is wrong.

CODE you posted is perfect!

Many thanks

I deleted older posts, so people who are looking for solution are not confused.