How to get all the "ProductsName" sold in a single transaction?

[#1]

HI community,

i 've a table with the following fields: Customer , Ticket Number, Date,Hour, Store, Amount, ProductName ,etc,

I have created an "univocal Key" in script, concatening the following fields: Customer & Ticket Number & Date

 

My goal is to get a table like this, with all the Products buyed in that visit..

I need, for each visit in the store, to get all the Products buyed.

I think the correct function is FirstSortedValue.

But i can't figure out the right sintax for my case.

I 've tried something like that:

Dimension: KEY (Customer & Ticket Number & Date)

Expresions:  FirstSortedValue (PRODUCTNAME, -DATE)   // to get the first Product sold in that transaction.

                    FirstSortedValue (PRODUCTNAME, -DATE,2)   // to get the second Product sold in that transaction.

                   FirstSortedValue (PRODUCTNAME, -DATE,3)   // to get the third Product sold in that transaction.

                and so on.....

[swuehl · Accepted solution]

Try to enumerate the Products bought per Key in your load script:

LOAD

     "univocal Key",

     Autonumber(RecNo(), "univocal Key") as NrItemBoughtInTransaction,

Then you can use this field either as  dimension in a (pivot) table, or e.g.like

=Only({<NrItemBoughtInTransaction  = {1}>} PRODUCTNAME)

=Only({<NrItemBoughtInTransaction  = {2}>} PRODUCTNAME)

=Only({<NrItemBoughtInTransaction  = {3}>} PRODUCTNAME)

in your table with dimension "univocal Key".

[#3]

That's the way i like it!

Thanks Swuehl, have a nice day!