I have a problem I'm trying to solve in different languages. I have a
solution in SQL and also in kdb which largely resembles the SQL solution.
I'm curious what a J solution would look like. More specifically, I'm
interested in picking the brains of others here to see if this type of
problem can be solved without looping (some form of scan?).
EDIT: Initially I wrote this up thinking the J solution would difficult,
but it was actually fairly straightforward -- about 15 minutes, but still
would like to see if there are alternatives. If nothing else, maybe an
interesting problem to share.
Example data:
A store has a transaction log with a sequence for each transaction. The
transaction log records a key for a unique customer/item combination. The
transaction log records how many units were purchased or returned.
Goal:
Attempt to match up related transactions and cancel out instances when the
customer/item combination is returned at the same quantity as a previous
transaction
Examples:
Joe buys 1 blue pen, which is defective, then returns the 1 defective blue
pen, then buys another blue pen. EXPECTED: cancel out first two
transactions and leave the the last one for 1 pen
Bob buys 2 red pens in two separate transactions. He then buys 3 more. He
returns the first two purchases as two separate return transactions.
EXPECTED: cancel out all transactions except the one for qty 3
Jane buys 5 purple pens and subsequently returns two of them. She buys two
more. EXPECTED: No transactions match exactly, so nothing is cancelled out
Data:
data=: 0 : 0
seq key qty
1 1 1
2 1 _1
3 1 1
4 2 1
5 2 1
6 2 3
7 2 _1
8 2 _1
9 3 5
10 3 _2
11 3 2
)
tbl =: ,. ' ' cut every cutLF data
'seqs keys qtys' =: |: ". every }. tbl
Goal:
goals =: 0 : 0
goal
cancelled
credit
ok
cancelled
cancelled
ok
credit
credit
ok
ok
ok
)
tbl,.(cutLF goals)
+---+---+---+---------+
|seq|key|qty|goal |
+---+---+---+---------+
|1 |1 |1 |cancelled|
+---+---+---+---------+
|2 |1 |_1 |credit |
+---+---+---+---------+
|3 |1 |1 |ok |
+---+---+---+---------+
|4 |2 |1 |cancelled|
+---+---+---+---------+
|5 |2 |1 |cancelled|
+---+---+---+---------+
|6 |2 |3 |ok |
+---+---+---+---------+
|7 |2 |_1 |credit |
+---+---+---+---------+
|8 |2 |_1 |credit |
+---+---+---+---------+
|9 |3 |5 |ok |
+---+---+---+---------+
|10 |3 |_2 |ok |
+---+---+---+---------+
|11 |3 |2 |ok |
+---+---+---+---------+
One approach:
applycredits =: 3 : 0
goals=.(<'goal')
creditids=.0
for_i. (i. # seqs) do.
key=.i{keys
seq=.i{seqs
qty=.i{qtys
nextcredit =.| {. qtys #~ ((key=keys)*(seqs>seq)*(qtys<0))
if. nextcredit = qty do.
goals=.goals,<'cancelled'
creditids =. creditids, seqs #~ ((key=keys)*(seqs>seq)*(qtys<0))
elseif. creditids e.~ seq do.
goals=.goals,<'credit'
elseif. do.
goals=.goals,<'ok'
end.
end.
goals
)
tbl ,. ( applycredits 0 )
+---+---+---+---------+
|seq|key|qty|goal |
+---+---+---+---------+
|1 |1 |1 |cancelled|
+---+---+---+---------+
|2 |1 |_1 |credit |
+---+---+---+---------+
|3 |1 |1 |ok |
+---+---+---+---------+
|4 |2 |1 |cancelled|
+---+---+---+---------+
|5 |2 |1 |cancelled|
+---+---+---+---------+
|6 |2 |3 |ok |
+---+---+---+---------+
|7 |2 |_1 |credit |
+---+---+---+---------+
|8 |2 |_1 |credit |
+---+---+---+---------+
|9 |3 |5 |ok |
+---+---+---+---------+
|10 |3 |_2 |ok |
+---+---+---+---------+
|11 |3 |2 |ok |
+---+---+---+---------+
(cutLF goals) -: ( applycredits 0 )
1
thanks for any input
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
