Cross Tab Report in QlikView

[#1]

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.

[#2]

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

[jldengra · Accepted solution]

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
	1102	475.00
405178	1101	1260.15
	1102	475.00
412792	1101	1590.50
	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

[Sokkorn]

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

[#5]

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

[#6]

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

[#7]

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

Don Saluga

Business Intelligence Analyst, Valuations and Vision Global Solutions