Alternative to Crosstable

[johnnyjohn]

Hi, 

How can I achieve the results of a crosstable without actually creating one? 

When I create a crosstable, the table is ~60mio lines of record because of the high cardinality and that messes up my performance. 

So currently I load my crosstable like this, and when I create my linechart, I just plot n5_fi_reversion_time as a dimension and avg(n5_fi_reversion_bps) as a measure.  

ExecutionCrossed:
CROSSTABLE(n5_fi_reversion_time,n5_fi_reversion_bps,1)
LOAD
n5_fi_uniqueid,
n5_fi_imp_min1000_bps as "1. -1s",
n5_fi_imp_min500_bps as "2. -0.5s",
n5_fi_imp_min100_bps as "3. -0.1s",
n5_fi_imp_0_bps as "4. 0.0s",
n5_fi_imp_100_bps as "5. 0.1s",
n5_fi_imp_500_bps as "6. 0.5s",
n5_fi_imp_1000_bps as "7. 1s"
RESIDENT ExecutionTemp;

Any help much appreciated

[QFabian]

Hi @johnnyjohn , maybe something like this :?

ExecutionCrossed:
LOAD
n5_fi_uniqueid,
'1. -1s'    as Range,
n5_fi_imp_min1000_bps as Time
RESIDENT ExecutionTemp;

LOAD
n5_fi_uniqueid,
'2. -0.5s"'    as Range,
n5_fi_imp_min500_bps as Time
RESIDENT ExecutionTemp;

LOAD
n5_fi_uniqueid,
'3. -0.1s'    as Range,
n5_fi_imp_min100_bps as Time
RESIDENT ExecutionTemp;

LOAD
n5_fi_uniqueid,
'4. 0.0s'  as Range,
n5_fi_imp_0_bps as Time
RESIDENT ExecutionTemp;

LOAD
n5_fi_uniqueid,
'5. 0.1s'    as Range,
n5_fi_imp_100_bps as Time
RESIDENT ExecutionTemp;

LOAD
n5_fi_uniqueid,
'6. 0.5s'    as Range,
n5_fi_imp_500_bps as Time
RESIDENT ExecutionTemp;

LOAD
n5_fi_uniqueid,
'7. 1s'    as Range,
n5_fi_imp_1000_bps as Time
RESIDENT ExecutionTemp;

[johnnyjohn]

For the reply Fabian, 

While this works, it seems to replicate the behavior of a CrossedTable, but doesn't actually solve the cardinality issue - the size of the table is the same. 

I was thinking something that would reduce the size of the table, or maybe even not create a table, using set analysis directly on the graph. Just not sure how one would do that. 

Thanks

[QFabian]

@johnnyjohn , If you load the table without the crosstable, in the expression of your chart you can use :

avg(
n5_fi_imp_min1000_bps +
n5_fi_imp_min500_bps +
n5_fi_imp_min100_bps +
n5_fi_imp_0_bps +
n5_fi_imp_100_bps +
n5_fi_imp_500_bps +
n5_fi_imp_1000_bps
)

[johnnyjohn]

What would my dimension be then? Correct me if I'm wrong but then I cannot break this down by individual time (-1s, -0.5s, etc...), which I need. Essentially I'd need a table/graph, which shows me each of the time horizons (all 7) and their associated average value . Thanks again

[vinieme12 · Accepted solution]

Use a dummy dimension for the line chart

ExecutionCrossed:
LOAD
n5_fi_uniqueid,
n5_fi_imp_min1000_bps as "1. -1s",
n5_fi_imp_min500_bps as "2. -0.5s",
n5_fi_imp_min100_bps as "3. -0.1s",
n5_fi_imp_0_bps as "4. 0.0s",
n5_fi_imp_100_bps as "5. 0.1s",
n5_fi_imp_500_bps as "6. 0.5s",
n5_fi_imp_1000_bps as "7. 1s"
RESIDENT ExecutionTemp;

dummyDim:

load * inline [

n5_fi_reversion_time, so_n5_fi_reversion_time

"1. -1s",	1
"2. -0.5s",	2
"3. -0.1s",	3
"4. 0.0s",	4
"5. 0.1s",	5
"6. 0.5s",	6
"7. 1s",	7

];

in Chart use dimension

n5_fi_reversion_time

Measure

=Pick(so_n5_fi_reversion_time , avg("1. -1s") , avg("2. -0.5s") , avg("3. -0.1s") .........)

[johnnyjohn]

Very clever, thank you!