User Profile
peiyezhu
Bronze Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: Finding Possible Matches to a Solution
//select rowid,* from Sheet1; with recursive D as ( select f01 E,rowid old_rowid,1 level,f01 和,rowid x from Sheet1 union all select E||'+'||f01,Sheet1.rowid,D.level+1,f01+和,x||','||rowid from Sheet1,D where rowid>old_rowid and round(f01+和,2)<=40) select row_number() over () no,* from D where round(和,2)=40 limit 500;Re: Excel - Matching outgoing to incoming inventory based a specific conditions
Auto allocate First In First Out batch number: 1,in running balance 2,out running balance 3,match in and out balance select * from Sheet2 limit 20; create temp table aa as select rowid old_rowid,*,row_number() over ( partition by SKU,PO) batch,sum(Pieces) over(order by rowid) in_balance from Sheet2 where Pieces>0; create temp table bb as select rowid old_rowid,*,sum(Total_out) over(order by rowid) balance from Sheet2 where Pieces=0; select * from aa; select * from bb; create temp table cc as select *,(select first_value(b.batch) over () from aa b where b.SKU=a.SKU and b.PO=a.PO and b.in_balance>=-a.balance) in_batch from bb a; select * from cc; select Sheet2.rowid,Sheet2.*,aa.batch,cc.in_batch from Sheet2 left join aa on Sheet2.rowid =aa.old_rowid left join cc on Sheet2.rowid=cc.old_rowid;Re: Excel - Matching outgoing to incoming inventory based a specific conditions
select * from Sheet2; create temp table aa as select rowid old_rowid,*,sum(`Total pieces`+`Total out`) over (partition by `Supplier PO`) Balance from Sheet2; create temp table bb as select *, case when Balance=0 then "closed" when Balance=`Total pieces` then "Open" else "In Process" end Status from aa; create temp table cc as select row_number() over () in_out,`Supplier PO` from bb group by `Supplier PO`; select iif(`Total out`<0,'',in_out) `In`,iif(`Total out`<0,in_out,'') Out,* from cc join bb using(`Supplier P O`);Re: Excel - Matching outgoing to incoming inventory based a specific conditions
create temp table aa as select *,sum(`Total pieces`+`Total out`) over (partition by `Supplier PO`) Balance from Sheet2; select *, case when Balance=0 then "closed" when Balance=`Total pieces` then "Open" else "In Process" end Status from aa;Re: HOW TO: "If cell contains specific text display the immediate next word after it"
select 1,* from Sheet1 union select 2,colIdxf[0:]{regexp2('(?<=-\s)\w+',%s) %s} from Sheet1; https://support.microsoft.com/en-us/office/regexextract-function-4b96c140-9205-4b6e-9fbe-6aa9e783ff57#:~:text=The%20REGEXEXTRACT%20function%20allows%20you%20to%20extract%20text,matches%20or%20capturing%20groups%20from%20the%20first%20match.Re: Help with formula
select * from My_✓ing; select * from AccountInf; create temp table aa as select Account,substr(f01,1,2) m,f01 `Date`,f04 Paid from My_✓ing a,AccountInf b where regexp(lower(b.key),lower(a.f02)); select * from aa; cli_one_dim~temp.aa~2; select * from aaunion; create temp table bb as select Account,m||'_'||属性 ColLab,数量 from aaunion; select * from bb; cli_create_two_dim~bb~ColLab~数量; select * from bb_two_dim; cli_stack_headers~bb_two_dim~(.+)_(.+);Re: Create 'parent' table to update data in 'child' worksheets + data in 'child' worksheets must tally
I guess a database web application with login is suitable for Similar situation as below: Due to various reasons, the customer's delivery date needs to be advanced or postponed. Every month, when making the production plan, the planner needs to inquire with the sales department about the adjusted delivery dates for all orders. If the planner sends the master sheet via email for each sales person to fill out, data leakage between different personnel may occur. Alternatively, the planner can send individual sheets to each sales person, but this results in multiple emails being sent and then merged into a master sheet. This process is time-consuming and labor-intensive. Currently available conditions: Each sales person has a fixed notebook login username. There is a shared network file folder within the company. The planner sets up a shared folder in the shared drive and shares the master sheet, setting read/write permissions for each sales person. What is desired: Each sales person logs into the shared network folder and opens the shared spreadsheet. They can only see the content under their own name in the sheet. They can only edit the adjusted delivery date column; the rest of the sheet's content is protected and only viewable.Re: Help Needed: Trouble Filtering and Re-Entering Data
Re: The UUID (last line) must be unique—no duplicate entries allowed. $query = " CREATE TABLE IF NOT EXISTS myList ( id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, UUID TEXT NOT NULL UNIQUE ); "; DDL key words unique. Re: The idea is to paste multi-line text into a text field. regular expression. var text = document.getElementById('inputText').value; var nameRegex = /(\d+g Name Bundle sib \d+ \d+[A-Z]*)/; var uuidRegex = /([a-f0-9-]{36})/; var nameMatch = text.match(nameRegex); var uuidMatch = text.match(uuidRegex);Re: Help! Removing a Space in Excel
reexreplace https://support.microsoft.com/zh-cn/office/regexreplace-%E5%87%BD%E6%95%B0-9c030bb2-5e47-4efc-bad5-4582d7100897#:~:text=%E4%BD%BF%E7%94%A8%20REGEXREPLACE%20%E5%87%BD%E6%95%B0%EF%BC%8C%E5%8F%AF%E4%BB%A5%E6%A0%B9%E6%8D%AE%E6%8F%90%E4%BE%9B%E7%9A%84%E6%AD%A3%E5%88%99%E8%A1%A8%E8%BE%BE%E5%BC%8F%20%28%E2%80%9Cregex%E2%80%9D%29%20%E5%B0%86%E5%AD%97%E7%AC%A6%E4%B8%B2%E4%B8%AD%E7%9A%84%E6%96%87%E6%9C%AC%E6%9B%BF%E6%8D%A2%E4%B8%BA%E5%8F%A6%E4%B8%80%E4%B8%AA%E5%AD%97%E7%AC%A6%E4%B8%B2%E3%80%82%20REGEXREPLACE%20%E5%87%BD%E6%95%B0%E6%9B%BF%E6%8D%A2%E6%89%80%E6%8F%90%E4%BE%9B%E7%9A%84%20%E6%96%87%E6%9C%AC,%E6%9B%BF%E6%8D%A2%E6%A8%A1%E5%BC%8F%20%E5%8C%B9%E9%85%8D%E7%9A%84%E5%AD%97%E7%AC%A6%E4%B8%B2%E3%80%82%20REGEXEXTRACT%20%E5%87%BD%E6%95%B0%E7%9A%84%E8%AF%AD%E6%B3%95%E4%B8%BA%EF%BC%9A%20REGEXREPLACE%20%28text%E3%80%81pattern%E3%80%81replacement%E3%80%81%20%5Boccurrence%5D%E3%80%81%20%5Bcase_sensitivity%5D%29
