Stock levels

[mitjap]

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.

[Taoufiq_Zarra · Accepted solution]

@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:

[mitjap]

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;

[Taoufiq_Zarra · Accepted solution]

@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;

[mitjap]

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.

[Taoufiq_Zarra]

😁😁😁

[Taoufiq_Zarra]

@mitjap 

where's the error, can you elaborate ?

[mitjap]

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.