topic Re: ODBC Data Load - field containing numeric and alphanumeric data in QlikView

ODBC Data Load - field containing numeric and alphanumeric data

AHSuser — Wed, 08 Sep 2021 17:03:07 GMT

Hello,

I have an ODBC connection between Smartsheet and Qlikview. There is one column of data that I am unable to completely bring into Qlikview successfully. The column contains primarily numeric data but has a few alphanumeric entries. The alphanumeric entries will not come into Qlikview; they show as blanks when I preview the data.

After reading through some other questions/answers, I believe I need a preceding load statement to classify this field as text, but I am uncertain as to the proper structure of the load statement/script, and I am uncertain as to where to place the load statement.

These are the first few lines in the script. Can anyone tell me how to write the line to get the "1 CPT/HCPCS Reported" field to come into Qlikview as text? Also, where in the statement would it need to be placed?

ODBC CONNECT TO SmartSheetConnector;
SQL SELECT "# of ICD-10 Codes Reported on Claim",
"Provider Name (Autopopulates)",
"Audit Project",
"1 CPT/HCPCS Reported",

Thank you!

Re: ODBC Data Load - field containing numeric and alphanumeric data

MayilVahanan — Thu, 09 Sep 2021 02:59:25 GMT

Did you try like below?

ODBC CONNECT TO SmartSheetConnector;

Load "# of ICD-10 Codes Reported on Claim",
"Provider Name (Autopopulates)",
"Audit Project",
Text("1 CPT/HCPCS Reported") as "1 CPT/HCPCS Reported";
SQL SELECT "# of ICD-10 Codes Reported on Claim",
"Provider Name (Autopopulates)",
"Audit Project",
"1 CPT/HCPCS Reported",

Re: ODBC Data Load - field containing numeric and alphanumeric data

AHSuser — Thu, 09 Sep 2021 11:58:23 GMT

Hello, Your load statement processed successfully without errors, but it didn't allow the alphanumeric values to come into Qlikview from Smartsheet. I did more research, and now I believe the issue lies with Smartsheet's Live Data Connector Default Settings . There are advanced options that lie within the connection properties with options to always return text, use majority rule, and use entirety rule. I'm going to ask our administrator to check our setting. It seems the entirety rule might correct this.

http://smartsheet-platform.github.io/odbc-docs/#advanced-options-menu

Your load statement was 100% perfect. It absolutely helped me troubleshoot the problem, and I'm going to save the load statement for later use.

I really appreciate your help Mayil!!!