topic Re: Column Filter in Qlik Replicate

Column Filter

rommel_oli — Tue, 13 Sep 2022 11:49:26 GMT

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.

Re: Column Filter

john_wang — Tue, 13 Sep 2022 12:04:09 GMT

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:

Hope this helps.

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

Regards,

John.

Re: Column Filter

john_wang — Tue, 13 Sep 2022 15:24:57 GMT

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.

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

Regards,

John.

Re: Column Filter

Heinvandenheuvel — Wed, 14 Sep 2022 03:50:34 GMT

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.

Re: Column Filter

rommel_oli — Wed, 14 Sep 2022 09:17:20 GMT

Hein,

I think Oracle VIEWS cannot be used to select TABLES.

Re: Column Filter

rommel_oli — Wed, 14 Sep 2022 09:24:10 GMT

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

Re: Column Filter

Heinvandenheuvel — Wed, 14 Sep 2022 11:35:23 GMT

>>> 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

Re: Column Filter

DesmondWOO — Wed, 14 Sep 2022 15:00:53 GMT

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

Re: Column Filter

john_wang — Wed, 14 Sep 2022 15:08:23 GMT

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.

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.

Re: Column Filter

rommel_oli — Thu, 15 Sep 2022 08:17:07 GMT

Yup, its Oracle limitation.

Re: Column Filter

Kohila — Mon, 15 Jul 2024 18:56:29 GMT

Hi Team, Any global transformation to apply in task level to remove a certain column in all the tables in task?

Re: Column Filter

john_wang — Mon, 15 Jul 2024 22:48:37 GMT

Hello @Kohila ,

Yes, Global Transform can drop column, see screen shot:

Hope this helps.

John.