Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

CrossTable

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

1 Solution

Accepted Solutions
rustyfishbones
Not applicable

Re: CrossTable

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

12 Replies
rustyfishbones
Not applicable

Re: CrossTable

Hi Omar,

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

Qlikview CrossTable in Script by RFB 72 - YouTube

regards

Al

Not applicable

Re: CrossTable

look here's an example:

Not applicable

Re: CrossTable

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

Regards!!!!

rustyfishbones
Not applicable

Re: CrossTable

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

Not applicable

Re: CrossTable

Here comes a chapter that tells you all of these tables

andrewpettit
Not applicable

Re: CrossTable

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.

Not applicable

Re: CrossTable

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!!!

andrewpettit
Not applicable

Re: CrossTable

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.

Not applicable

Re: CrossTable

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

regards!!!