Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
.
.
.
.
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
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
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.
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))