Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

lmychajluk
New Contributor

Import Script for Codes from Excel that could be Scientific Notation

I have a relatively simple load that I'm trying to do from an XLSX file:

LOAD DISTINCT Value as ID,

     Description as Desc

FROM

[$(path_File_Data)\myfile.xlsx]

(ooxml, embedded labels, table is LIST);

In the XLSX file, the IDs are in the format of 0x## (zero, a letter, then 2 or maybe 3 digits. Ex. - 0D45). These are Segment codes coming from Oracle Financials, FWIW. For most of the values this isn't a problem. But, it seems that in the Excel sheet the values that start with 0E## are considered numeric (and Excel precedes the value with a single quote to force it to appear as a text value, which it doesn't do for the other IDs). When I try to import them with the script above, I get a 0 as the ID for all the 0E## values, presumably because it's a tiny number that just gets rounded to 0. If I try to force the import as Text with the Text() function, all the IDs come in as '0000'.

I'm not generating the XLSX file, so I can't easily modify my source data.

Is there anything I can do in the load script itself to get the ID values to load as '0E01', '0E02', etc...???

Thanks for any help!

-Lee

Community Browser