topic Help wanted!!! I have to import vertical data into horizontal recordset. How can I do this? in QlikView

Help wanted!!! I have to import vertical data into horizontal recordset. How can I do this?

Mon, 07 Nov 2011 19:36:14 GMT

There is the table CustOrdValues in the MSSQL-database:

Fields: customer, order, kind of value, amount

There are 10 recordsets per customer and order and I need all the amounts in one single recordset like:

customer, order, amount of kind1, amount of kind2, amount of kind3, amount of kind4, amount of kind5, amount of kind6, ...

I think it should be very easy but I have no idea...

Thank for quick answers..

Jörn

Help wanted!!! I have to import vertical data into horizontal recordset. How can I do this?

Mon, 07 Nov 2011 19:59:03 GMT

I don't think it's easy ! I wait for a long time to find a solution.

In your case, I create a pivot table with 3 dimensions : Customer, Order, Kind

Then I put Kind in the header of the pivot

Then I export the file in excel

Then I read the new file

Not very satisfy but it works !

Help wanted!!! I have to import vertical data into horizontal recordset. How can I do this?

Mon, 07 Nov 2011 20:06:28 GMT

Hi Jörn,

If you simply want to display the data in QlikView with columns customer, order, amount of kind1, amount of kind2 etc. you could load the original table unaltered, and then create a pivot table in QlikView.

Let's say you had the following data:

Just load it as it is into QlikView. Then create a pivot table with dimensions Customer, Order and Kind, and the expression sum(Amount).

Then drag the Kind column to the top, which would give you:

Is that what you were after?

Kai

Re: Help wanted!!! I have to import vertical data into horizontal recordset. How can I do this?

johnw — Mon, 07 Nov 2011 20:08:52 GMT

It sounds like you'll want to do a generic load followed by a loop to combine the generic tables back into a single table. See the attached example of how to do that. Here's the script.

[Table]:
LOAD * INLINE [
Invoice No.,Product
1,ball
1,spoon
2,shirt
2,spoon
2,ticket
3,shirt
];
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]
;
[Final]:
LOAD DISTINCT "Invoice No." // add other key fields to this list
RESIDENT [Table]
;
FOR I = nooftables() TO 0 STEP -1
    LET vTable = tablename(I);
    IF wildmatch('$(vTable)','Generic.*') THEN
        OUTER JOIN ([Final])
        LOAD *
        RESIDENT [$(vTable)];
        DROP TABLE [$(vTable)];
    END IF
NEXT

DROP TABLE

;

Help wanted!!! I have to import vertical data into horizontal recordset. How can I do this?

Mon, 07 Nov 2011 20:14:58 GMT

John's come back ! Great !

I was sure the solution was tricky but it's a great one.

Thanks John

Help wanted!!! I have to import vertical data into horizontal recordset. How can I do this?

Mon, 07 Nov 2011 20:34:37 GMT

Thank vou very much John and khs,

John's solution works fine. That ist what I was looking for. It is not as easy as I thought 🙂

Best wishes from Hambrug, Germany

Jörn