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

[RichJ]

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

[john_wang]

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.

[Heinvandenheuvel]

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.

[RichJ]

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

[RichJ]

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

[Heinvandenheuvel · Accepted solution]

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

[RichJ]

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!