How to do this?

[#1]

Hello I have the following table:

Invoice No. Product

1 ball

1 spoon

2 shirt

2 spoon

2 ticket

3 shirt

...........................................

How can I convert it to:

Invoice No. product product product .................

1 ball spoon

2 shirt spoon ticket

3 shirt

.

.

.

.

[#2 · Accepted solution]

Hi Svilen

Not exactly what you asked for but might be good enough?

it gives you the products in separate product columns for each invoice, with many products this might not be very useful

Juerg

[johnw]

Well, fields in the table can't have the same name, so you can't just use product, product and product. And also, we normally LIKE tables in your initial format. But if you really want to change it, one way that kind of does what you want is a generic load.

LEFT JOIN ([Table])
LOAD *
,if("Invoice No."=previous("Invoice No."),peek("Product Number")+1,1) as "Product Number"
RESIDENT [Table]
ORDER BY "Invoice No."
;
[Generic]:
GENERIC LOAD
"Invoice No."
,'Product ' & "Product Number" as "Product Number"
,"Product"
RESIDENT [Table]
;
DROP TABLE

;

[#4]

Thanks to both of you.

Actually I don't need to put field labels on top I want to have a table in which each row to start with invoice No. and after that all products associated with this invoice.

Thanks again for the help.

[johnw]

Ah. Well a very simple approach, though it doesn't keep separate columns, is to simply concatenate:

Dimension = Invoice No.
Expression = concat(Product,', ')

To keep separate columns, you could make a pivot table like this:

Dimension 1 = Invoice No.
Dimension 2 = valueloop(1,3)
Expression = subfield(concat(Product,','),',',valueloop(1,3))