Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, i want to create an expression for the following....
we have several thousand transaction ID numbers (Transid) with a number of products against each (SalesQuantity). I would like to create an expression which will only display TRANSIDs with 2 or more SALESQUANTITY against them. Its gets more difficult as some transaction id's have duplicates (but with a different product against it) so i would like it to total up the duplicate Transid's.
E.g
Transid SalesQuantity
211125 1
211125 3
211131 2
211139 1
211144 1
i would like the expression only to display the Transid:
211125 (as this totals to 4 products)
211131 (as this totals to 2)
but not to display the latter 2 Transid's as these are less than 2.
Can anyone help??
hi,
try the following in your script
tab1:
LOAD * INLINE [
TransId, SalesQuantity
211125, 1
211125, 3
211131, 2
211139, 1
211144, 1
];
load TransId,sum(SalesQuantity) as sum resident tab1 group by TransId;
thanks
Hi, thanks for your reply, the data given in my original post is an example. i have thousands of transaction id's and they grow by several thousand every day, so typing the id's in a script obviously can't be done.
Does anyone have any other ideas?
As tauqueer said:
ResultTable:
load
TransId,
sum(SalesQuantity) as sumQuantity
resident TransactionTable
group by TransId;