topic Re: set up a global rule to replace '' with ' ' in Qlik Replicate

set up a global rule to replace '' with ' '

RichJ — Sat, 16 Mar 2024 17:45:19 GMT

Hi Sir,

I have a large number of tables with some column data type "STRING(1)", where we need to ensure if the value is '' (empty space) then re-set it as ' ' (one space).

It can be done by individual table transformation; but it world be idea to setup a global rule to achieve it. I tried numerous cases for global rules like below, but none worked out -

case
when $AR_M_SOURCE_DATATYPE_NAME = 'STRING' and $AR_M_SOURCE_DATATYPE_LENGTH = 1 and $AR_M_SOURCE_COLUMN_NAME =''
THEN $AR_M_SOURCE_COLUMN_NAME = ' '
ELSE $AR_M_SOURCE_COLUMN_NAME
END

Appreciate the help,

Richard

Re: set up a global rule to replace '' with ' '

john_wang — Sun, 17 Mar 2024 02:41:54 GMT

Hello @RichJ ,

Thanks for reaching out to Qlik Community!

Not sure what's the source/target DBs types and the exact datatype in the table, so let's focus on the Global Transformation in Qlik Replicate now. Below is a sample:

1. New a Global Transformation rule "Replace column value"

2. Define the Transformation scope to STRING(1) by using

Data type is : STRING

Scope Expression: $AR_M_SOURCE_DATATYPE_LENGTH == 1

If the source column length is not 1 then the '' will NOT be replaced by ' '. Certainly you may add additional constraints eg schema name/table name/column name pattern etc.

3. Using Null Check function ifnull(), the expression is:

ifnull($AR_M_SOURCE_COLUMN_DATA,' ')

Hope this helps.

John.

Re: set up a global rule to replace '' with ' '

Heinvandenheuvel — Mon, 18 Mar 2024 02:24:25 GMT

Just a brainfart it seems, or maybe not the most recent version.

@RichJ wrote - and $AR_M_SOURCE_COLUMN_NAME =''

But you need to set and get the columns DATA, not it's name.

Use: $AR_M_SOURCE_COLUMN_DATA

Note, this variable is NOT available/documented in early Replicate 'V7' versions but it is in 2023.11

Going in though the GUI you start with: Transformation Type - Column - Replace column value

Hein.

Re: set up a global rule to replace '' with ' '

RichJ — Tue, 19 Mar 2024 17:51:58 GMT

Hi John,

Thanks for help! The source db is SQL Server, the target is Snowflake.

If we have more than one global rules, what is the order they got executed?

If we have both global rules and table transformations, will global rules get executed before table transformation?

Thanks,

Richard

Re: set up a global rule to replace '' with ' '

RichJ — Tue, 19 Mar 2024 18:03:39 GMT

Hi Hein,

Our server version is "2023.11.0.159", but there is no "$AR_M_SOURCE_COLUMN_DATA" available in global rule configuration page. However, I simply went ahead and used "$AR_M_SOURCE_COLUMN_DATA" , and it seemed working - I am just wondering if that's reliable for me to use it even it's not shown in the config page?

Thanks,

Richard

Re: set up a global rule to replace '' with ' '

Heinvandenheuvel — Tue, 19 Mar 2024 19:52:38 GMT

@RichJ <but there is no "$AR_M_SOURCE_COLUMN_DATA" available in global rule configuration page. >

I verified just now under 2023.5.0152.

You have to select "Replace column value" along the way otherwise the "data" tab will not show.

Re: set up a global rule to replace '' with ' '

RichJ — Wed, 20 Mar 2024 22:17:10 GMT

It seems to me if there are both global rule and table transformation presented, then the global rule will not be executed? Please confirm. Thanks!