Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ;
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
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
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 ;
John's come back ! Great !
I was sure the solution was tricky but it's a great one.
Thanks John
JJ
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