Qlik Community

Ask a Question

Qlik Sense App Development

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

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
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

0 Replies