Is it possible to script transformations for many tables?

[BFasano]

A vendor recently updated their database and increased the varchar length on 178 fields across 131 tables. 

As a quick fix, we are going to trim and truncate the fields using the built in transformation options, but with this many I wanted to know if there was a way to script these changes in Qlik Replicate? We are using Qlik Replicate May 2023 (2023.5.0.285)

(Very beginner here, the people that were trained in Qlik have recently left the company and I'm being asked to fix this)

Thanks in advance!

-Brian

[Dana_Baldwin · Accepted solution]

Hi @BFasano 

Please check the Global Transformation option which could be utilized to modify columns for any/all tables in the task:

Defining global rules | Qlik Replicate Help

If this is not a good fit, exporting the task(s) and adding the transformation to the JSON file may also be an option for you. Modify one column manually within Qlik Replicate and export the task to get the proper syntax/format for the JSON modification.

Thanks,

Dana

[BFasano]

Thanks @Dana_Baldwin 

Some of the fields have the same name, but most are different. There's not really a pattern to the field names. 
I could create a few global rules and get a good majority of the fields that changes. 

I was thinking more of a SQL Query, but I don't know what the backend of Qlik is. It looks like it may be Postgres, but I don't see a specific Qlik database, just Postgres. 

[Dana_Baldwin]

Hi @BFasano 

No, there isn't a way to directly query or modify the database that Qlik Replicate uses, at least not for this purpose. There are some occasions where we need to modify it directly using an editor for troubleshooting or a work around. Qlik Replicate uses SQLite as its database. There are multiple files used for different purposes.

Thanks,

Dana

[BFasano]

@Dana_Baldwin 

OK, thank you. Is there any way to "promote" these changes between environments? I'm doing these in our dev environment first, then we need them in Test, and eventually in Production. 

Thanks, 

-Brian

[Dana_Baldwin]

Hi @BFasano 

You can export the task JSON file from dev that has the right configuration then import it to the higher environments.

Thanks,

Dana

[Heinvandenheuvel]

Are you sure you even need transformations ??

Typically, when a column length is changed that change is applied to the target and life goes on.

Whether this works for you can only be advised with more background - notably source DB, target DB, sample transformation.

Perhaps you intend for the target to remain the same and just want to truncate (SUBSTR) to be sure that on table re-create (reload) it stays the same as pre-application-update?

If you do indeed need to apply many manipulation to many columns over many tables you may just want to take a deep breath and 'just do it' - once..

However, if you expect this may well happen again or needs to be done in other environment where you cannot just import json's or when you are like me, preferring to spend 2 days automating over 1 day just doing it - then you might prefer to write a script.

A while back I posted a PERL script to automate reporting and modifying manipulations. Even if you don't want to use it (PERL!) then you may want to try to read it to understand what can/must be done.

https://community.qlik.com/t5/Qlik-Replicate/Task-JSON-file/td-p/2157435

Hein.

[BFasano]

Thanks Hein, 

We're doing it as an interim fix, until we can update the target tables and all downstream systems. The data is going into our ODS and Qlik is writing to the Raw tables, it then gets normalized into the staging layer and then further combined into the consumption layer. Finally some of data is integrated to various systems. 

All in, we're looking at several hundred stored procedures / queries that will need to be modified. 

I was able to set up a few Global Rules, as most of the fields had the same name. That left aout 20 fields that needed to be done manually. 

-Brian