OK, so you have some source data that is pivoted and you want to unpivot it to work for charts within QlikView (and applies to QlikSense).

The source data contains columns across month rows that I want to aggregate, but the columns have to be a single dimension to represent the source of the traffic.

What I will end up with in the QlikView model is a table of 3 columns (YearMonth, Source, Traffic)

The load script has been split into two parts for clarity to demonstrate the process, so you can simply

copy and paste into your QV Document or QS App.

The method we use to unpivot this data is the CROSSTABLE prefix , there is more Help in the Qlik help texts, but fundamentally we execute the prefix as shown below.

From the Qlik online Help ...

Crosstable

The crosstable prefix is used to turn a cross table into a straight table, that is, a wide table with many columns is turned into a tall table, with the column headings being placed into a single attribute column.

Syntax:

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

// Step 1 - Load up the pivoted traffic data

TrafficStaged: // Here I create a temp table to hold the raw data LOAD date_unit, google, // Note the source columns are all named as the source website qlik, // and these columns hold the traffic value which I want to transform [1337x], kat, facebook, linkedin, awari, vrl, youtube, Other FROM [C:\YourDataSourceDirectory\PageVisitsBySource.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

// Step 2 - Now we have the staging table loaded , lets load the final table that will be used for our

// data visualisations

Traffic: CROSSTABLE(Source,Traffic,1) // Here I issue the CROSSTABLE prefix and instruct it to do ...

// Transform the column e.g. Google to be stored in [Source]

// Transform the value of e.g Google to be stored in [Traffic]

// The 1 specifies the [date_unit] is the qualifier field LOAD date_unit as YearMonth, // I rename date_unit to be YearMonth google, qlik, [1337x], kat, facebook, linkedin, awari, vrl, youtube, Other RESIDENT // As I already have the data loaded in memory, I tell the load

TrafficStaged; // get the data from TrafficStaged hence the use of RESIDENT

// predicate

drop Table TrafficStaged; // Drop the staging table as it is no longer required

That's all there is too it, of course you can do this in Step 1, but I just wanted to introduce Resident to the mix, you will run in to this requirement frequently.

The below screen shot is the data Before and After.

Coming soon (late May 2016) my QlikView 12.0 developers course, I will detail this in the course too.

For early bird discounts get in touch with me.

Have a great day wherever you are.

Cheers

Paul

#QlikSense #QlikView #CrossTable