topic Re: CrossTable returning too few rows in QlikView

CrossTable returning too few rows

Anonymous — Thu, 04 Oct 2012 13:15:15 GMT

Hi - i'm using a crosstable as below.

The first query returns the right amount of rows (rows * columns)

The second query returns less than what i expect, when i round my volumes - the end result looks fine, I'm curious what QV is doing to not return as many rows as I'd expect.

Thanks,

Nandy.

Query 1

CrossTable(WEEK, VOLUME, 6)

LOAD CHANNEL,

PROPOSITION,

ITEM,

[1],

[2],

[3],

[4]

from tab;

Query 2

CrossTable(WEEK, VOLUME, 6)

LOAD CHANNEL,

PROPOSITION,

ITEM,

round ([1],0.001) as 1,

round ([2],0.001) as 2,

round ([3],0.001) as 3,

round ([4],0.001) as 4

from tab;

Re: CrossTable returning too few rows

Thu, 04 Oct 2012 13:58:37 GMT

Not sure why but I do know that Weeks field needs to be converted into numerical values first. Crosstables do something that leaves them in a String state. Use an expression like this to convert them;

You can insert the Round outside of num# which converts into a numerical value.

Date(Num#(Weeks),'DD-MMM-YY') as Weeks

I'm sure someone has a better explanation but I know this works for me. I struggled with this issue as well.

Re: CrossTable returning too few rows

thomas_skariah — Fri, 05 Oct 2012 08:22:28 GMT

Hi Nandy,

Is this a two different tables with similar columns?

Regards,

Tom

Re: CrossTable returning too few rows

Anonymous — Fri, 05 Oct 2012 08:53:24 GMT

Hi Tom, no, its the same table.

Nandy

Re: CrossTable returning too few rows

thomas_skariah — Fri, 05 Oct 2012 09:21:35 GMT

ok, Create a table for crosstab,and then using resident put the condition (in this case round).

Regards,

Tom

Re: CrossTable returning too few rows

Anonymous — Fri, 05 Oct 2012 13:26:16 GMT

Hi - this works, both methods end with the same final number of rows, so at least I can be confident the end result is correct.

Still it would be interesting to understand what QV is doing to give the difference.

Thanks

Nandy

Re: CrossTable returning too few rows

Thu, 21 Nov 2013 13:40:43 GMT

Hi Nandy,

Will you be able share the data file and the qlikview for a better analysis?

Vinoth