topic Help with adding an expression in QlikView

Help with adding an expression

hopkinsc — Thu, 17 Jun 2010 16:17:28 GMT

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??

Help with adding an expression

Thu, 17 Jun 2010 17:23:50 GMT

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

Help with adding an expression

hopkinsc — Fri, 18 Jun 2010 08:51:32 GMT

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?

Help with adding an expression

Fri, 18 Jun 2010 10:58:52 GMT

hi, you can store your transactionid's and salesquantity in excel and load that excel file instead of wrirting all the id's in script tab1: load TransId,SalesQuantity from abc.xls ; tab2: load TransId,sum(SalesQuantity) as sum from abc.xls group by TransId; thanks

Help with adding an expression

sparur — Fri, 18 Jun 2010 11:03:19 GMT

As tauqueer said:

ResultTable:

load

TransId,

sum(SalesQuantity) as sumQuantity

resident TransactionTable

group by TransId;