topic How to do this? in QlikView

How to do this?

Wed, 02 Jun 2010 14:48:11 GMT

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

How to do this?

Wed, 02 Jun 2010 15:43:41 GMT

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

How to do this?

johnw — Wed, 02 Jun 2010 20:53:33 GMT

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

;

How to do this?

Thu, 03 Jun 2010 07:35:45 GMT

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.

How to do this?

johnw — Thu, 03 Jun 2010 16:54:31 GMT

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