Independent Columns as a Dimension

[#1]

Hey all,

Looking for some help. I have a data set in excel with a list of company transactions in rows. In columns we have the transaction detail. Part of the data we want to look at is who we do business with. We store this data in independent columns, each customer has its own column, as many transactions have more than one customer.

For example, if we do business, order #15, with Bob and Joe, there will be a row for order #15, and a column for Bob and a Column for Joe, the row gets a 1 in Bobs column, and a 2 in Joes column.

I want to graph in qlikview Bob and Joe on the x axis and be able to click into each to drill down. Is this possible?

I'm looking for just a basic bar chart that aggregates total sales from Bob and Joe, so X axis is customer, and Y axis is total dollar amount. Any help would be appreciated, the problem comes from this data being in separate columns.

Let me know, thanks guys.

Sameer

[#2]

Hi,

The solution for this could be :

1) transform the data so that you have a new column called "customer" which will have names of customer.

2) another new column "amount" which stores the amount for respective customer.

The table structure should look like:

transaction_id, customer, amount

Once you have this, create a bar chart with dimension as 'customer' and expression as 'sum(amount)'

Thanks,

Amit

[#3]

Sameer,

I believe you can work with CROSSTAB LOAD. Here is an example I just put together.

Sales:
LOAD * INLINE [
 OrderNo, Amount, Alice, Bob, Joe
 1, 24, 1, , ,
 2, 67, , 1, 2
 3, 35, 1, 2,
 4, 11, , , 1
];

Sales_T1:
CROSSTABLE ( Customer, SoldTo, 2 )
LOAD
 *
RESIDENT
 Sales;

Sales_pivot:
LOAD
 OrderNo,
 Customer,
 Amount
RESIDENT
 Sales_T1
WHERE
 isnum( SoldTo );

DROP TABLES Sales, Sales_T1;

Table Sales represents your input data. Data are loaded into an intermediate table Sales_T1, which introduces a column Customer. The exact syntax of the Crosstab statement is described in the manual. Basically, it tells QV to treat the first 2 columns (OrderNo and Amount, in may example) as normal columns, and view the rest as a pivot table.

Sales_T1 still has a row for each order and each customer, hence I use another LOAD statement to pick only the rows that have a number in their OrderNo/Customer cell.

Caveat: If you do it this way, and you compute the overall Sum(Amount), the revenue is counted double if you have two customers per order. You need take to care of this in your application code.

Hope this helps,

Hellmar

[#4]

Under the Sales table - it seems as though you manually inputted all of the data within the column headers OrderNo, Amount, Alice, Bob, Joe. Is that necessary? Can I just list the column headers?

[#5]

I did a LOAD INLINE just for the test. Of course, in real life you will have something like

LOAD
 OrderNo,
 Amount,
 Alice,
 Bob,
 Joe
FROM Sales.csv (...)

depending on your data source. LOAD INLINE is just a handy way to generate test data on the fly.

[#6]

Ah, that makes sense, sorry, complete new user here trying to get something done for management.

Another question - in your crosstable line which column did you want SoldTo to refer to in my case? I think Customer is a newly generated column, correct?

[#7]

In CROSSTABLE ( Customer, SoldTo, 2 ) LOAD ..., both Customer and SoldTo are generated columns.

The 2 means: the leftmost 2 columns are treated as real columns, not parts of the matrix.

The Customer column is the place where the column headers go (i.e. Alice, Bob, Joe).

All the matrix values go to the SoldTo column.