Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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

13-06-2016 10-06-02.jpg 

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.

13-06-2016 10-03-23.jpg

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

1 Solution

Accepted Solutions
MVP
MVP

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

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".

2 Replies
MVP
MVP

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

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".

Not applicable

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

That's the way i like it!

Thanks Swuehl, have a nice day!

Community Browser