Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

1 Solution

Accepted Solutions
MVP
MVP

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

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 Replies
Not applicable

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

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

Not applicable

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

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:

jorn_data.png

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:

jorn_pivot.png

Is that what you were after?

Kai

MVP
MVP

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

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

;

Not applicable

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

John's come back ! Great !

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

Thanks John

JJ

Not applicable

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

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

Community Browser