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

[#1]

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

[#2]

Hi

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 !

JJ

[#3]

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

[johnw · Accepted solution]

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

;

[#5]

John's come back ! Great !

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

Thanks John

JJ

[#6]

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