topic Re: 2 axis fields in QlikView

2 axis fields

Mon, 20 Jan 2014 21:21:44 GMT

Sorry I am not sure what it was called, but normally you have a fine data-structure with fields/columns horizontally in for instance a spreadsheet, and then data is "running" vertically. But what if I have fields both ways?:

Can QlikView deal with this, as it is, that is without changing the data-structure?

Re: 2 axis fields

Mon, 20 Jan 2014 22:12:11 GMT

Hi,

What would you want to do? At some point, this array will have to be changed into a classical table, but there are several ways to do it and achieve what you want. For example, rows and columns could be like coordinates, each couple of coordinates returning a number; you'd have a classical table then...

Re: 2 axis fields

maxgro — Mon, 20 Jan 2014 22:56:09 GMT

Qlikview can load yours data in its memory database and maintain the original structure; the result table is

or (with a crosstab load) loads in its memory database the same data changing the original structure to that table

Re: 2 axis fields

Mon, 20 Jan 2014 23:27:47 GMT

Thx you both.

At the user interface, I want the user to choose first field (horizontal) and then second field (vertical), and then the user get some visual representation for instance on a map, but first when I have selected both. So it could be warehouses, A-A is internal, A-B is from terminal A to terminal B, and so forth.

BTW: how do I do the crosstab load?

Re: 2 axis fields

Mon, 20 Jan 2014 23:48:15 GMT

If 2nd field (vertical) is destination terminal, you can for example put Destination as a header for the first column in your source, then:

Crosstable (Start, TravelTime)

load *

from (source);

This will make a table with columns Destination, Start (A, B, C), and travel time in the 3rd column like Massimo Grossi showed.

This can be done with an Excel array, an Inline load etc.

Re: Re: 2 axis fields

maxgro — Mon, 20 Jan 2014 23:51:12 GMT

Example with your data in attachment

CrossTable(A, Data)

LOAD *

FROM

[104783.xlsx]

(ooxml, embedded labels, table is Sheet1);

From qlikview online help

A cross table is a common type of table featuring a matrix of values between two or more orthogonal lists of header data, of which one is used as column headers. A typical example could be to have one column per month. To transform a cross table into a straight table, use a crosstable prefix. The result is that the column headers (e.g. month names) will be stored in one field - the attribute field - and the column data (month numbers) will be stored in a second field: the data field.

The syntax is:

crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )

where:

attribute field name is the field to contain the attribute values.

data field name is the field to contain the data values.

n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.

Examples:

Crosstable (Month, Sales) Load * from ex1.csv;

Crosstable (Month,Sales,2) Load * from ex2.csv;

Crosstable (A,B) Select * from table3;