Consider a warehouse operating with an initial value of 0 and following a First-In, First-Out (FIFO) approach. We want to determine the source of each output. For example, all of Output A is supplied by the first input on 12/06. However, for Output B, part is supplied by the input on 21/06, with the remainder coming from the input on 13/06.
📌 Challenge Details and Links
Challenge Number: 130
Challenge Difficulty: ⭐⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of First In First Out (Fifo)! with Power Query
Power Query solution 1 for First In First Out (Fifo)!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
F = each Table.SelectRows(Source, (r) => Text.StartsWith(r[Tyoe], _)),
R = Table.ToRows(F("O")),
S = Table.FromRows(
List.TransformMany(
R,
each Table.ToRows(
Table.Group(
Table.FirstN(
Table.Skip(
Table.Combine(
Table.AddColumn(F("I"), "Q", each Table.Repeat(Table.FromRecords({_}), [Quantity]))[
Q
]
),
List.Sum(List.Zip(List.FirstN(R, List.PositionOf(R, _))){2}? ?? {0})
),
_{2}
),
"Date",
{"Q", Table.RowCount}
)
),
(i, _) => {Text.Split(i{1}, " "){1}, i{0}} & _
),
{"Output", "Registered Date", "Source Date", "Quantity"}
)
in
SPower Query solution 2 for First In First Out (Fifo)!, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Input = Table.SelectRows(Source, each [Type] = "Input"),
Output = Table.SelectRows(Source, each [Type] <> "Input"),
Generate = List.Generate(
() => [N1 = - 1, N2 = - 1, RIQ = 0, ROQ = 0],
each [N1] < Table.RowCount(Input) and [N2] < Table.RowCount(Output),
each [
N1 = [N1] + Number.From(C1),
N2 = [N2] + Number.From(C2),
IR = Input{N1},
ID = IR[Date],
IQ = IR[Quantity],
OR = Output{N2},
OT = Text.AfterDelimiter(OR[Type], " "),
OD = OR[Date],
OQ = OR[Quantity],
C1 = [RIQ] = 0,
C2 = [ROQ] = 0,
QU = List.Min({if C2 then OQ else [ROQ], if C1 then IQ else [RIQ]}),
RIQ = (if C1 then IQ else [RIQ]) - QU,
ROQ = (if C2 then OQ else [ROQ]) - QU
],
each [Output = [OT], Registered Date = [OD], Source Date = [ID], Quantity = [QU]]
),
Return = Table.FromRecords(List.Skip(Generate))
in
ReturnPower Query solution 3 for First In First Out (Fifo)!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
List1 = List.RemoveLastN(
Table.ToColumns(
Table.ExpandListColumn(
Table.TransformColumns(
Table.SelectRows(Source, each Text.Contains([Tyoe], "Input")),
{"Quantity", each List.Repeat({1}, _)}
),
"Quantity"
)
),
2
),
List2 = List.Reverse(
List.RemoveLastN(
Table.ToColumns(
Table.ExpandListColumn(
Table.TransformColumns(
Table.SelectRows(Source, each not Text.Contains([Tyoe], "Input")),
{{"Quantity", each List.Repeat({1}, _)}, {"Tyoe", each Text.End(_, 1)}}
),
"Quantity"
)
)
)
),
Tbl = Table.SelectRows(
Table.FromColumns(List2 & List1, {"Output", "Register Date", "Source Date"}),
each [Output] <> null
),
Sol = Table.Group(Tble, Table.ColumnNames(Tbl), {{"Quantity", each Table.RowCount(_)}})
in
SolPower Query solution 4 for First In First Out (Fifo)!, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(A, "Rep", each List.Repeat({1}, [Quantity])),
C = Table.ExpandListColumn(B, "Rep"),
D = Table.AddColumn(C, "Grp", each Text.BeforeDelimiter([Tyoe], " ")),
E = Table.Sort(D, {{"Grp", 1}, {"Tyoe", 0}, {"Date", 0}}),
F = Table.Group(E, {"Grp"}, {{"All", each Table.AddIndexColumn(_, "Idx", 1)}}),
G = Table.Combine(Table.Sort(F, {"Grp", 1})[All]),
H = Table.AddColumn(
G,
"Source Date",
each Table.SelectRows(G, (x) => x[Idx] = [Idx] and x[Tyoe] = "Input")[Date]{0}
),
I = Table.SelectRows(H, each ([Tyoe] <> "Input")),
J = Table.RenameColumns(I, {{"Date", "Registered Date"}, {"Tyoe", "Output"}}),
K = Table.TransformColumns(J, {"Output", each Text.AfterDelimiter(_, " ")}),
L = Table.Group(
K,
{"Output", "Registered Date", "Source Date"},
{"Quantity", each List.Sum([Rep])}
)
in
LSolving the challenge of First In First Out (Fifo)! with Excel
Excel solution 1 for First In First Out (Fifo)!, proposed by Bo Rydobon 🇹🇭:
=LET(z,
B3:D13,
i,
FILTER(
z,
INDEX(
z,
,
2
)="input"
),
j,
TAKE(
i,
,
-1
),
k,
SCAN(
,
j,
SUM
)-j,REDUCE(F2:I2,
DROP(
z,
,
2
),
LAMBDA(a,
q,
LET(t,
+q:C13,LET(b,
SUMIFS(
q:D3,
TAKE(
q:C3,
,
1
),
"o*"
)-k,
c,
b-q,d,
IF(b>j,
j,
b*(b>0))-IF(c>j,
j,
c*(c>0)),IF(
@t="input",
a,
VSTACK(
a,
CHOOSE(
{1,
2,
3,
4},
RIGHT(
@t
),
@+B13:q,
FILTER(
TAKE(
i,
,
1
),
d
),
FILTER(
d,
d
)
)
)
))))))Excel solution 2 for First In First Out (Fifo)!, proposed by Oscar Mendez Roca Farell:
=LET(d,
B3:B13,
q,
C3:D13,
t,
C3:C13,
P,
DROP,
W,
BYROW,
F,
LAMBDA(
a,
FILTER(
q,
LEFT(
t
)=a
)
),
i,
F(
"I"
),
o,
F(
"O"
),
r,
P(
REDUCE(
1-P(
o,
,
1
)^0,
-P(
i,
,
1
),
LAMBDA(
i,
x,
HSTACK(
i,
P(
REDUCE(
x,
W(
i,
SUM
)-P(
o,
,
1
),
LAMBDA(
j,
y,
VSTACK(
j,
-MAX(
y,
SUM(
j
)
)
)
)
),
1
)
)
)
),
,
1
),
G,
LAMBDA(
b,
c,
TOCOL(
IFS(
r,
TOROW(
XLOOKUP(
W(
b,
CONCAT
),
W(
q,
CONCAT
),
c
)
)
),
2
)
),
HSTACK(G(
o,
RIGHT(
t
)
),
G(
o,
d
),
G(
i,
d
),
TOCOL((1/r)^-1,
2)))Excel solution 3 for First In First Out (Fifo)!, proposed by Kris Jaganah:
=LET(
a,
B3:B13,
b,
C3:C13,
c,
D3:D13,
d,
FILTER(
HSTACK(
a,
c
),
b="Input"
),
e,
--TEXTSPLIT(
CONCAT(
REPT(
TAKE(
d,
,
1
)&" ",
TAKE(
d,
,
-1
)
)
),
,
" ",
1
),
f,
FILTER(
HSTACK(
a&"-"&RIGHT(
b
),
c
),
b<>"Input"
),
g,
TEXTSPLIT(
CONCAT(
REPT(
TAKE(
f,
,
1
)&" ",
TAKE(
f,
,
-1
)
)
),
"-",
" ",
1
),
h,
--TAKE(
g,
,
1
),
GROUPBY(
HSTACK(
TAKE(
g,
,
-1
),
h,
TAKE(
e,
ROWS(
g
)
)
),
h,
COUNT,
,
0
)
)Solving the challenge of First In First Out (Fifo)! with Python
Python solution 1 for First In First Out (Fifo)!, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-130 FIFO.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows=1, nrows=11)
test = pd.read_excel(path, usecols="F:I", skiprows=1, nrows=9).rename(columns=lambda x: x.replace('.1', ''))
i_data = input[input['Tyoe'].str.startswith('I')].copy()
i_data = i_data.loc[i_data.index.repeat(i_data['Quantity'])].assign(In=1, rn=lambda x: range(1, len(x) + 1)).reset_index(drop=True)
o_data = input[input['Tyoe'].str.startswith('O')].copy()
o_data = o_data.loc[o_data.index.repeat(o_data['Quantity'])].assign(Out=1, rn=lambda x: range(1, len(x) + 1)).reset_index(drop=True)
all_data = pd.merge(i_data, o_data, on='rn', how='outer').dropna().sort_values(by='rn').reset_index(drop=True)
all_data = all_data.groupby(['Date_x', 'Date_y', 'Tyoe_x', 'Tyoe_y'], as_index=False)['In'].sum()
all_data["Output"] = all_data['Tyoe_y'].str[-1]
all_data = all_data.drop(columns=['Tyoe_x', 'Tyoe_y']).reindex(columns=['Output', 'Date_y', 'Date_x', 'In'])
all_data.columns = test.columns
print(all_data.equals(test)) # True
Solving the challenge of First In First Out (Fifo)! with Python in Excel
Python in Excel solution 1 for First In First Out (Fifo)!, proposed by Alejandro Campos:
import deque
data = xl("B2:D13", headers=True)
warehouse, output_sources = deque(), []
for _, row in data.iterrows():
date, type_, quantity = row["Date"], row["Type"], row["Quantity"]
if type_ == "Input":
warehouse.append((date, quantity))
elif type_.startswith("Output"):
output_name, remaining_quantity = type_.split()[1], quantity
while remaining_quantity > 0 and warehouse:
input_date, input_quantity = warehouse.popleft()
used_quantity = min(input_quantity, remaining_quantity)
output_sources.append((output_name, date, input_date, used_quantity))
remaining_quantity -= used_quantity
if input_quantity > used_quantity:
warehouse.appendleft((input_date, input_quantity - used_quantity))
df_output_sources = pd.DataFrame(output_sources, columns=["Output", "Registered Date", "Source Date", "Quantity"])Solving the challenge of First In First Out (Fifo)! with R
R solution 1 for First In First Out (Fifo)!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-130 FIFO.xlsx"
input = read_excel(path, range = "B2:D13")
test = read_excel(path, range = "F2:I11")
i_data = input %>%
filter(str_starts(Tyoe, "I")) %>%
uncount(Quantity, .remove = F) %>%
mutate(In = 1, rn = row_number())
o_data = input %>%
filter(str_starts(Tyoe, "O")) %>%
uncount(Quantity, .remove = F) %>%
mutate(Out = 1, rn = row_number())
all = full_join(i_data, o_data, by = "rn") %>%
summarise(all = sum(In, na.rm = T), .by = c(Date.x, Date.y, Tyoe.x, Tyoe.y)) %>%
na.omit() %>%
mutate(Output = str_sub(Tyoe.y, -1, -1)) %>%
select(Output, `Registered Date` = Date.y, `Source Date` = Date.x, Quantity = all)
all.equal(all, test, check.attributes = F)
#> [1] TRUE Solving the challenge of First In First Out (Fifo)! with Google Sheets
Google Sheets solution 1 for First In First Out (Fifo)!, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=493757374#gid=493757374