Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rommel_oli
Contributor II
Contributor II

Column Filter

I have a table with more than 500 columns. Since Qlik is limited to only 500 columns per table, is it possible to not load and replicate other columns in Qlik replicate? I am using Oracle as source and target. 

Labels (3)
2 Solutions

Accepted Solutions
john_wang
Support
Support

Hello @rommel_oli ,

In table settings , you can remove some columns from the replication. see transformations for a single table .

A sample: select the column and press button "<" to remove the column:

john_wang_0-1663070041433.png

Hope this helps.

BTW, any detailed info about the max columns is 500? And what's the Replicate version.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

john_wang
Support
Support

Hello @rommel_oli ,

I have a table with more than 500 columns. Since Qlik is limited to only 500 columns per table...


There is not such limitation in Replicate. Oracle  Maximum Columns  per table is 1000. SQL Server Maximum Columns per table is 1024. In SQLite (it's Replicate Repository DB), the maximum columns number per table is 2000.

The 1000 columns tables are replicating successfully in Replicate, include replication from Oracle to Oracle, and from Oracle to SQL Server.

john_wang_0-1663082689679.png

 

Looks like you are meaning something else. let us know more details.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

9 Replies
john_wang
Support
Support

Hello @rommel_oli ,

In table settings , you can remove some columns from the replication. see transformations for a single table .

A sample: select the column and press button "<" to remove the column:

john_wang_0-1663070041433.png

Hope this helps.

BTW, any detailed info about the max columns is 500? And what's the Replicate version.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
john_wang
Support
Support

Hello @rommel_oli ,

I have a table with more than 500 columns. Since Qlik is limited to only 500 columns per table...


There is not such limitation in Replicate. Oracle  Maximum Columns  per table is 1000. SQL Server Maximum Columns per table is 1024. In SQLite (it's Replicate Repository DB), the maximum columns number per table is 2000.

The 1000 columns tables are replicating successfully in Replicate, include replication from Oracle to Oracle, and from Oracle to SQL Server.

john_wang_0-1663082689679.png

 

Looks like you are meaning something else. let us know more details.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist II
Specialist II

Hmm,

I don't think there is a hard 500 column limit. I do believe there is a maximum SQL statement length of perhaps 32KB (or 64KB) which can be exceeded using 500 longish (20 - 30 bytes ) column names.

Just create a test? 

If it is the statement length, and also if it is the column count, maybe a view can be used as source instead of the actual table renaming, or skipping, certain columns?

Hein.

rommel_oli
Contributor II
Contributor II
Author

Hein, 

I think Oracle VIEWS cannot be used to select TABLES. 

rommel_oli
Contributor II
Contributor II
Author

John, 

The 500 columns is due to Qlik limitation of creating the statement which is 31K chars which I believed is reached when column names are almost around 30 chars limit. I have tables with 800 columns but not having the issue since column names are relatively small. What i noticed is when most of the column names are at least 25 chars and the values are lengthy. 

Thanks for sharing the way to filter out columns. I will try this one. I am thinking of creating 2 tables in the target and join them together via a view as the final result. 

Rommel

Heinvandenheuvel
Specialist II
Specialist II

>>>  due to Qlik limitation of creating the statement which is 31K chars 

I could easily be wrong but I don't think it is  a Qlik Replicate limitation, but an (Oracle) limitation. Look carefully to the error message. That's not to say Replicate could possibly be smarter about it.

Hein

 

 

DesmondWOO
Support
Support

Sounds it is Oracle limitation. I did following test with a table which has 1000 columns and each column name is 30 characters long.

Oracle -> Oracle
Replicate reported ORA-01460: unimplemented or unreasonable conversion requested.

Oracle -> SQL Server
Record can be inserted successfully.

Desmond

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
john_wang
Support
Support

Hello @rommel_oli , copy @Heinvandenheuvel ,

You are right. there is limitation in Replicate: if many columns name are long enough, then the composed INSERT SQL cannot be run, got error:

 

[ASSERTION       ]W:  Long Oracle statements (longer than 32K) cannot be applied, causing a data error ORA-01460

 

This is Replicate limitation instead of Oracle limitation. We have some options for it:

1- as you said, remove some columns. or

2- rename the columns name in transformation, eg shorten some of the columns name to make sure the whole SQL length less than 32K chars. For example I shorten the column name from

COLUMNNAMEISLOOOOOOOOOOOOOG001 to COL001

and apply the transformation to all columns, sample as below. then it works perfect. 

john_wang_0-1663167450291.png

3- split the table into multiple tasks, or use materialized view.

However as the renaming columns name is a once job and it solve the problem completely, this is the best choice in my opinion. Certainly it's a boring job to operate so many columns name in GUI,  an easier way is exporting the task to JSON, edit/modify the JSON then import it back to Replicate repository. IN fact this is what I did  in my labs test.

Best Regards,

John.

 

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
rommel_oli
Contributor II
Contributor II
Author

Yup, its Oracle limitation.