Num datatype in Qlik

[Bharathi09]

Hello All,
I have below Qlik Script

trim(transit)&' '&if(len(KeepChar(trim(sup),'0123456789'))=len(trim(sup)),num(trim(supsup)))),trim(sup))&' '&trim(sup_loc) as Key_Col

I want to show above script in Snowflake
But when I am using To_Number for num(trip(sup)) it's giving error as Numeric value 'RK150005889' is not
recognized

But how can I convert exact Qlik script to SF?

Please help
Thanks!

[TauseefKhan]

Hi @Bharathi09,

Value 'RK150005889' cannot be converted because it contains non-numeric characters.

Use the REGEXP function in Snowflake to check if the string contains only numeric characters.

In Above expression KeepChar(trim(sup), 'KeepChar(trim(sup), '0123456789')')-  Keeps only the numeric characters from the trimmed sup field.
                 

= len(trim(sup))- Checks if the length of the string with only numeric characters is equal to the length of the trimmed sup string. If true, it means sup contains only numeric characters.

 num(trim(supsup)) If the condition is true, it attempts to format the supsup field as a number after trimming it. If the condition is false, it simply returns the trimmed sup field.

WHEN REGEXP_LIKE(TRIM(sup), '[0-9]') THEN TRY_TO_NUMBER(TRIM(sup))
ELSE TRIM(sup)
END || ' ' ||
TRIM(sup_loc) AS Key_Col

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****

[MarcoWedel]

can you give some examples of what this expression is supposed to do, as interpreting a string as numeric value only to concatenate its string representation with other strings does not seem to make much sense anyways ... ?

[Bharathi09]

Hello,
Thanks fro replying, when tried your query, giving below error

Numeric value 'R0022721' is not recognized

Please help
Thanks!

[TauseefKhan]

Check with this:
SELECT
TRIM(transit) || ' ' ||
CASE
WHEN REGEXP_LIKE(TRIM(sup), '^[0-9]+$') THEN TRY_TO_NUMBER(TRIM(sup))
ELSE TRIM(sup)
END || ' ' ||
TRIM(sup_loc) AS Key_Col
FROM YourTable;

[Bharathi09]

No still not working, giving same error

[TauseefKhan]

As in the Qlik expression, the KeepChar() function in Qlik is used to return a string consisting of the characters from the first input string that are also present in the second input string. 

Example 1: Keep only numeric characters
KeepChar('a1b2c3', '123')      // Returns '123'

So check the Function related to Snowflake.

Check this :

SELECT
TRIM(transit) || ' ' ||
COALESCE(
CASE
WHEN LENGTH(REGEXP_REPLACE(TRIM(sup), '[^0-9]', '')) = LENGTH(TRIM(sup))
THEN TO_CHAR(TO_NUMBER(TRIM(sup)))
ELSE NULL
END,
TRIM(sup)
) || ' ' ||
TRIM(sup_loc) AS Key_Col
FROM YourTable;

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.****

[Bharathi09]

Unfortunately same error it's showing

[PrashantSangle]

@Bharathi09 , your question is more around snowflake sql function. I will suggest post your question on snowflake community, you will get more expert over there.

https://community.snowflake.com/s/forum

If you need explanation of qlik expression then @TauseefKhan , already explain in detail on his 1st response.

Regards,

Prashant Sangle