Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

The Crosstable Load

There are a number of prefixes in QlikView, that help you load and transform data. One of them is the Crosstable transformation.

Whenever you have a crosstable of data, the Crosstable prefix can be used to transform the data and create the desired fields. A crosstable is basically a matrix where one of the fields is displayed vertically and another is displayed horizontally. In the input table below you have one column per month and one row per product.

Crosstable transformation4.png

But if you want to analyze this data, it is much easier to have all numbers in one field and all months in another, i.e. in a three-column table. It is not very practical to have one column per month, since you want to use Month as dimension and Sum(Sales) as measure.

Enter the Crosstable prefix.

It converts the data to a table with one column for Month and another for Sales. Another way to express it is to say that it takes field names and converts these to field values. If you compare it to the Generic prefix, you will find that they in principle are each other’s inverses.

The syntax is

   Crosstable (Month, Sales) Load Product, [Jan 2014], [Feb 2014], [Mar 2014], … From … ;

There are however a couple of things worth noting:

  • Usually the input data has only one column as qualifier field; as internal key (Product in the above example). But you can have several. If so, all qualifying fields must be listed before the attribute fields, and the third parameter to the Crosstable prefix must be used to define the number of qualifying fields.
  • It is not possible to have a preceding Load or a prefix in front of the Crosstable keyword. Auto-concatenate will however work.
  • The numeric interpretation will not work for the attribute fields. This means that if you have months as column headers, these will not be automatically interpreted. The work-around is to use the crosstable prefix to create a temporary table, and to run a second pass through it to make the interpretations:

   tmpData:

   Crosstable (MonthText, Sales)

   Load Product, [Jan 2014], [Feb 2014], … From Data;

   Final:

   Load Product,

      Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,

      Sales

      Resident tmpData;

   Drop Table tmpData;

Finally, if your source is a crosstable and you also want to display the data as a crosstable, it might be tempting to load the data as it is, without any transformation.

I strongly recommend that you don’t. A crosstable transformation simplifies everything and you can still display your data as a crosstable using a standard pivot table.

HIC

68 Comments

Thanks for sharing....

0 Likes
813 Views
jason_michaelid
Honored Contributor II

Thanks Henric. I find Crosstable() one of the most useful functions in QlikView but it does have one drawback - it is very slow. It does seem to work multi-threaded, but each core is only very slightly utilized. Can you shed some light on how this function works at a CPU-level?

Thanks again,

Jason

0 Likes
813 Views
thornofcrowns
Valued Contributor II

Useful and interesting as always, thanks!

0 Likes
813 Views
sudeepkm
Valued Contributor III

Hi Henric,

Thanks a lot for the post.

I think here we have to be careful in writing count(Product) as once we create the cross table the Product is repeated. So it has to be count(Distinct Product) to get the number of unique products.

One more thing is how to handle a scenario where we expect more columns to be in coming. like

in your example suppose in the month of Aug 2014 the source will have one more column for Jul2014?

0 Likes
813 Views
Employee
Employee

You are right that Count(distinct Product) is the correct way to count products here.

If you have two different numbers for each month, e.g. no of units and sales amount, then you need to load two tables from the same source:

Crosstable (Month, Sales)

   Load Product, [Sales Jan 2014] as [Jan 2014], [Sales Feb 2014] as [Feb 2014], … From … ;

Crosstable (Month, Units)

   Load Product, [Units Jan 2014] as [Jan 2014], [Units Feb 2014] as [Feb 2014], … From … ;

HIC

813 Views
sudeepkm
Valued Contributor III

Hi Henric,

Thanks a lot for your response. I still have this question for you.

The Source file will change every month by one additional column presenting the Recent Month.

For example in the month of July 2014 the source file has Jan-2014 till Jun-2014 columns but in Aug-2014 the source file will change and will have one more column as Jul-2014.

I would like to take your advice how to handle this type of Source files in QlikView.

Is it like I may have to put an if else block which checks the current month and based on which it will have a cross table load script.

In that case I may end up in creating 12 different script blocks per every month. Thanks in advance.

0 Likes
813 Views

When you have increasing months, you can use

LOAD *

-Rob

813 Views
jason_michaelid
Honored Contributor II

Sudeep,

Just use a LOAD * in your Crosstable() script. That will add months in automatically as they are added to the table. Be aware, of course, that if any other unwanted fields are added to the source table they will also be added to the Crosstable().

Jason

EDIT: Just seen Rob has answered the same already! Sorry

0 Likes
813 Views
sudeepkm
Valued Contributor III

Thanks a lot. I got it.

0 Likes
813 Views
jimshakes
New Contributor III

Crosstable is very useful but I've found that it's only useful with small amounts of data, when you scale the data up you start hitting performance issues and this turns out to be extremely slow.

Is there anything that can be done to help speed it up or is this just a known issue/Crosstable should only ever be used for smaller amounts of data?

Thanks

813 Views
MVP
MVP

Thanks Henric.

I find that there are often further manipulations you need to do after doing your crosstable load.  A common one I have seen is where accountants put the year on it's own row above the Jan column (or in a merged cell) and then repeat that for each year.  You then find when you load with * as Rob and Jason suggest you end up with fields Jan ... Dec, Jan2 ... Dec2, Jan3 ...

The resident load then needs to get a bit cleverer and do something like:

Date(Date#('20' & if(len(MonthText) = 3, '13', '0' & mid(MonthText, 4, 1)), mid(MonthText, 1, 3) & '01', 'YYYYMMDD'), 'DD MMM YYYY') as Month,

There is generally a way to deal with even the most untidy and inconsistent CROSSTABLE.  I have had to in the past load the column headings into a table (using a TRANSPOSE laod) and then pick which of those headings make up the column list - building a string to use in the load script.  All good fun!

Steve

0 Likes
813 Views
male_carrasco
Contributor

Nice post Henric!

0 Likes
813 Views
robert99
Valued Contributor II

When I download a date into Excel it can be converted (formatted) as a number (and then back to a date etc)

Crosstables convert dates to text

These can be converted in QlikView as DATE (NUM# (InvoiceDate)) as InvoiceDate ,

But when I download into excel its downloaded as text. And I have been unable to change this using whatever combination I have tried eg num (date# etc

I overcame this problem but using Value to convert the text to a value in Excel (this works). But what is the best way to format in QV to overcome this issue (not having to use value in Excel)

edit  it works. Will at least OPDate2 does

date (num# (OPDate)) as OPDate ,
date (num#(OPDate)) as OPDate2,

Strange. I guess I just need to give the date a different name (as OPDate3 works too

813 Views
subhasmita_kar
Contributor

Very useful..thanks!!

0 Likes
813 Views
egnaldo_sousa
Contributor II

Hello HIC

Very Good

Is possible to do unlike?

Example:

Imagem 2.png

0 Likes
813 Views
Employee
Employee

That is the topic for next week's blog post...

But if you can't wait until then, look for "Generic" in the help.

HIC

813 Views
egnaldo_sousa
Contributor II

OK, very good, I will search to about it is subject....

But I am anxious for look your next post

0 Likes
813 Views
MVP
MVP

I seem to recall that Rob Wunderlich has posted on this before, on his QlikView Notes blog.

0 Likes
813 Views
prodanov
Contributor

But to know Generic Load generate separate table for each value in Column "Field". To create only one table is necessary to join them

0 Likes
813 Views
egnaldo_sousa
Contributor II

Hello Dimetar

Do you have an example?

Tks

0 Likes
813 Views
prodanov
Contributor

No I don't. But I created small application for you, but I don't now how to upload it in this blog. For my applications, I use function that I build to automate this process.

Best regards

813 Views
egnaldo_sousa
Contributor II

Can you to send on my e-mail?

dfgui2000@hotmail.com

tks

0 Likes
813 Views
Employee
Employee

The Crosstable transformation can be slow for large data amounts, an it is mainly due to the fact that it isn't multi-threaded.

HIC

0 Likes
813 Views
klaus_feldam
Contributor II

Brilliant, Henric.

Thanks for sharing.

0 Likes
813 Views
Sajid_Mahmood
Contributor

Hi HIC,

I am also looking for the solution similar to Egnaldo's has requested above. I have table with Amount, Hours, Kilowatts which needs to be converted to crosstable.

Regards

Sajid

0 Likes
813 Views
Employee
Employee
0 Likes
813 Views
Not applicable

hi Rob/Henric,

I  am still having an issue here.

The source file has new columns added(attribute columns).

I do a reload on the qlikview application and the data from the new columns does not appear.

Not sure if i am doing something wrong here.

I do use the following

test_data:

crosstable(build,power,2)

Load * from... .;

Currently, i am using the following version of the qlikview.

qlikview: 11.20.12235  SR5 X64 version.

thanks in advance.

Ramesh

0 Likes
813 Views
Employee
Employee

It sounds as if you are doing the right thing, but it is impossible to say for certain without the data and the app.

HIC

0 Likes
813 Views
geetaalhan
Contributor

Hi,

Well explained.

Thanks for sharing.

0 Likes
813 Views
Not applicable

Hi Henric,

Thanks! Very helpful article.

T.P.S

0 Likes
813 Views