topic Re: CrossTable in QlikView

CrossTable

Thu, 31 Oct 2013 21:04:50 GMT

Hi everyone!!!

Can you explain me and get a little EXAPLE how does it works or use the CROSS TABLE?? I am so sorry I don't understand!!!

Nothing about that

Re: CrossTable

rustyfishbones — Thu, 31 Oct 2013 21:06:20 GMT

Hi Omar,

Please check out this video, it may help you get started

Qlikview CrossTable in Script by RFB 72 - YouTube

regards

Re: CrossTable

Thu, 31 Oct 2013 21:09:51 GMT

look here's an example:

Re: CrossTable

Thu, 31 Oct 2013 21:11:59 GMT

How do it Works????? can you upload de excel file? plis

Regards!!!!

Re: CrossTable

rustyfishbones — Thu, 31 Oct 2013 21:15:05 GMT

Omar,

If you have an excel file where the data is stored with Values in 1 column and the dimensions in the rows you would use Crosstable in your script

It's a bit like using Transpose in Excel i suppose

Check out the video link

Re: CrossTable

Thu, 31 Oct 2013 21:25:35 GMT

Here comes a chapter that tells you all of these tables

Re: CrossTable

andrewpettit — Thu, 31 Oct 2013 22:10:54 GMT

Crosstable is conceptually the reverse of a Pivot... to take a pivot table type data-set and consume it into a generally more friendly format for storage, aggregation, and processing you would Crosstable it.

If you have

ID, Jan, Feb, Mar

1, 5, 6, 5

2, 5, 4, 4

and you Crosstable('Month','Value',1) in QlikView you will get

ID, Month, Value

1, Jan, 5

1, Feb, 6

1, Mar, 5

2, Jan, 5

2, Feb, 4

2, Mar, 4

Once this has been done its now easier to filter by month since each month is no longer a column name but instead a value in a column, and aggregation is easier as well since I can just say "sum(Value)" vs "sum(Jan)+sum(Feb)+sum(Mar)".

Hope this explains it simply enough.

Re: CrossTable

Thu, 31 Oct 2013 22:17:23 GMT

Thanks I understand this part perfect just please CAN YOU EXPLAIN ME a little MORE

for example the part of the.

Qualifier Fields:

crosstable(Month,Sale, 2)

what happened if I change the number after of Sale Colum for example the 2?

can you explain me please?

Regards!!!

Re: CrossTable

andrewpettit — Thu, 31 Oct 2013 22:46:08 GMT

The number of qualified fields is the number of fields at the beginning (left most side) of your table, which you do not want to have cross tabled. So in my example above I had a qualifier of one because ID was my only non-crosstabled field. If my header looked like this:

ID, Dept, Jan, Feb, Mar

I would want a qualified fields value of 2 so that both ID and Dept would not be crosstabled and Jan, Feb, Mar would be. If I messed up and still used a one the value of "Dept" would erroneously be put into my Month column as a value and the Dept name would be put into my Value field which is most likely not what most would want.

Re: CrossTable

Thu, 31 Oct 2013 22:49:35 GMT

THANKS ANDREW I understand but Can you show me an basic example where is CORRECT to USE more Qualifier Fields?

regards!!!

Re: CrossTable

andrewpettit — Thu, 31 Oct 2013 22:54:31 GMT

Maybe your consuming a spreadsheet of employee related expenses and they

are enter under the the following format...

Employee Name, Expense Type, 10/20/2013, 10/21/2013, 10/22/2013,

10/23/2013, 10/24/2013, 10/25/2013, 10/26/2013

Re: CrossTable

Thu, 31 Oct 2013 23:23:55 GMT

Check this;

http://community.qlik.com/message/415724?et=watches.email.thread#415724

I understand PERFECT NOW thanks VERY MUCH EVERYONE!!!!

FOR YOUR HELP

Regards!!!!

Re: CrossTable

Wed, 26 Nov 2014 22:55:09 GMT

Hi Omar,

I have created a tutorial that includes a section on using a crosstable.

The complete tutorial is here: QlikView 11 Developer Tutorial

The crosstable piece is here: QlikView 11 Developer Tutorial - part 7

Mike