topic Re: Cross Tab Report in QlikView in QlikView

Cross Tab Report in QlikView

Mon, 11 Nov 2013 22:22:57 GMT

I have a QlikView Report that shows the following dimensions.

Order LineNumber Amount

412792 1101 1590.50

412792 1102 475.00

403778 1101 1272.55

403778 1102 475.00

405178 1101 1260.15

405178 1102 475.00

etc....

But I would like to make the report display this way...more of a cross tab report.

Order	1101	1102
412792	$1,590.50	$475.00
403778	$1,272.55	$475.00
405178	$1,260.15	$475.00
400268	$990.00	$475.00
405156	$1,166.35	$475.00
404125	$1,640.45	$475.00

I've tried numerous ways, but cant' seem to come up with the right fix. Any help would be appreciated. Thanks community.

Re: Cross Tab Report in QlikView

Mon, 11 Nov 2013 23:04:33 GMT

You can create the Pivot Table. Please find the attached file as reference.

Re: Cross Tab Report in QlikView

jldengra — Mon, 11 Nov 2013 23:29:23 GMT

Hello, Don.

You only need to use a pivot table and "pivot" your Line Number column to be transposed.

To see this in action, I loaded your data into an inline table:

Orders:

LOAD * INLINE [

Order, LineNumber, Amount

412792, 1101, 1590.50

412792, 1102, 475.00

403778, 1101, 1272.55

403778, 1102, 475.00

405178, 1101, 1260.15

405178, 1102, 475.00

];

After reloading the data, I created a new chart, chosing the type "Pivot Table", and selecting Order and LineNumber as dimensions, and Amount as expression, and checked the option to Expand All to see all the columns expanded, and the result is this table:

Order	LineNumber	= Amount
403778	1101	1272.55
403778	1102	475.00
405178	1101	1260.15
405178	1102	475.00
412792	1101	1590.50
412792	1102	475.00

Then, clicking on "LineNumber" and moving it to the right and top until the arrow changes its shape up to left, the LineNumber column is transposed and the data is shown as you wish.

Order	LineNumber	1101	1102
403778		1272.55	475.00
405178		1260.15	475.00
412792		1590.50	475.00

Re: Cross Tab Report in QlikView

Sokkorn — Tue, 12 Nov 2013 01:17:47 GMT

Hi Don,

We have two method to make this happen. Let try

1. Method1: Using Pivot Table

a. Suppose that you have data table above

Order LineNumber Amount

412792 1101 1590.50

412792 1102 475.00

b. Create Pivot Table with

i. Dimension: Order and LineNumber

ii. Expression: Sum(Amount)

c. Expend all then click and hold on LineNumber field; drag it to the top.

2. Method2: Using script level

[DS]:
LOAD * Inline [
Order1,     LineNumber1,        Amount1
412792,     1101,              1590.50
412792,     1102,              475.00
403778,     1101,              1272.55
403778,     1102,              475.00
405178,     1101,              1260.15
405178,     1102,              475.00];
[TMP]:
GENERIC LOAD * RESIDENT [DS];


[RESULT]:
LOAD DISTINCT Order1 RESIDENT [DS];


DROP TABLE [DS];


FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) AS Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'TMP1.*');
NEXT i


FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i


DROP Table TableList;

Regards,

Sokkorn

Re: Cross Tab Report in QlikView

Tue, 12 Nov 2013 14:53:04 GMT

Thank you all for your help. I am trying each of your solutions. That's what makes the QlikView community so great.

Re: Cross Tab Report in QlikView

Tue, 12 Nov 2013 15:13:46 GMT

Thank you for taking the time to respond to my question. I got tangled up thinking valuelist match pick etc.

Re: Cross Tab Report in QlikView

Tue, 12 Nov 2013 15:21:19 GMT

Thank you for your post. I’ll consider it for my solution.

Don Saluga

Business Intelligence Analyst, Valuations and Vision Global Solutions