Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@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 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 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:
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;
@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;
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.
😁😁😁
where's the error, can you elaborate ?
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.