Qlikview Cross table is not working records count 38 Million.

[Qlikbuddy]

I am trying to convert 5 columns into rows. and I have around 38 Million records(19 months data) in a single table with 21 columns.

(excluding the converted columns). just loaded 3 months of data and tried to use cross table and its taking huge time and also hanging and in the cross table we can see that this works fine upto 100 thousand records.

what is the limitation which cross table, is it possible to use it for 38 million records...?

or can i request them to do this Unpivoting in the data base it self and create a final table..?

many thanks in advance.

[mpc]

Hi, 

AFAIK, crosstable does not have any limitation. It's seem to be a RAM/CPU limitation. 
Then the best way to solve this is to unpivot the table in the DB as you suggested. 

Best

[marcus_sommer · Accepted solution]

A crosstable load is a quite heavy transformation and it will need some resources but with enough available RAM it will finish the task. Monitoring the task-manager during the execution should showing what happens.

If it are only 5 columns you may skip the crosstable feature and doing the transformation manually, like:

t: load F1 ... F22, F23 as Value, 'F23' as Category from X;
concatenate(t) load F1 ... F22, F24 as Value, 'F24' as Category from X;
concatenate(t) load F1 ... F22, F25 as Value, 'F25' as Category from X;
...

Beside the above I suggest to consider to apply an incremental approach and transforming only the new data and loading the historic ones from a qvd.

[Qlikbuddy]

thanks a lot for the response Marcus_Sommer. would love to hear from you. thank again for your contributions to the Qlik community from many years.