CrossTable returning too few rows

[Anonymous]

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;

[#2]

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.

[thomas_skariah]

Hi Nandy,

Is this a two different tables with similar columns?

Regards,

Tom

[Anonymous]

Hi Tom, no, its the same table.

Nandy

[thomas_skariah]

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

Regards,

Tom

[Anonymous]

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

[#7]

Hi Nandy,

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

--

Vinoth