topic Re: How can I replicate Text to Columns from Excel in Qlik script? in QlikView

How can I replicate Text to Columns from Excel in Qlik script?

cliff_clayman — Mon, 24 Sep 2018 16:53:25 GMT

I have an Excel file that I am bringing in to QlikView and creating a QVD. Prior to using Qlik, I ran a macro on the Excel data to do Text to Columns on some of the columns. I was able to successfully write in the script to use the Text() function on a few on the columns, but it is not working for one in particular. The values in this column are mixed, meaning that there are some numbers and some text values. When I run the script and put the field into a List Box, the number values have .000000 after them and I need to eliminate that.

Re: How can I replicate Text to Columns from Excel in Qlik script?

m_woolf — Mon, 24 Sep 2018 18:03:16 GMT

Can you provide a sample Excel wkbk?

Re: How can I replicate Text to Columns from Excel in Qlik script?

vishsaggi — Mon, 24 Sep 2018 18:07:14 GMT

may be use below if not can you tell me what is your expected output?

Fabs(Num#(Yourfield))

Re: How can I replicate Text to Columns from Excel in Qlik script?

cliff_clayman — Mon, 24 Sep 2018 18:11:01 GMT

The expected output with the provided screen shot would be....

60-89963-00

415

416

417

418

439

439N5900199739

Re: How can I replicate Text to Columns from Excel in Qlik script?

cliff_clayman — Mon, 24 Sep 2018 18:14:35 GMT

I attached a sample of the Raw data to my reply below.

Re: How can I replicate Text to Columns from Excel in Qlik script?

m_woolf — Mon, 24 Sep 2018 18:26:27 GMT

When I read from your sample wkbk, I don't see the issue you are describing.

This is my load script:

LOAD Reference,

[Reference Key]

FROM

(ooxml, embedded labels, table is Data);

Here is a screenshot of the resulting data in a table box:

Re: How can I replicate Text to Columns from Excel in Qlik script?

cliff_clayman — Mon, 24 Sep 2018 18:44:22 GMT

I got the same thing. Not sure why it would be different in my main app. It looks different here...

Re: How can I replicate Text to Columns from Excel in Qlik script?

m_woolf — Mon, 24 Sep 2018 19:23:49 GMT

Open Settings|Document|Properties|Number.

Select the Reference field and set Number precision to 14.

Re: How can I replicate Text to Columns from Excel in Qlik script?

cliff_clayman — Mon, 24 Sep 2018 19:25:58 GMT

The field needs to be Text as the values are not just numbers. Also, I still get bad values when I make that change/

Re: How can I replicate Text to Columns from Excel in Qlik script?

m_woolf — Mon, 24 Sep 2018 19:30:41 GMT

Those items pictured are numbers displayed in Scientific Notation formatting.

Re: How can I replicate Text to Columns from Excel in Qlik script?

cliff_clayman — Mon, 24 Sep 2018 19:33:54 GMT

Yes, but they should not be displayed like that. I need the actual value to be displayed.

Re: How can I replicate Text to Columns from Excel in Qlik script?

m_woolf — Mon, 24 Sep 2018 19:42:04 GMT

The Scientific Notation is not the actual value, it is the formatted value. Excel applies the formatting automatically because of the column width.

If you insist on keeping the longer numbers in Scientific Notation, then I suggest that you read all the data as Text and strip off the '.000000'

Re: How can I replicate Text to Columns from Excel in Qlik script?

cliff_clayman — Mon, 24 Sep 2018 19:43:20 GMT

Yes, that was my original intention for posing this discussion thread. I'm not sure how to do that.

Re: How can I replicate Text to Columns from Excel in Qlik script?

m_woolf — Mon, 24 Sep 2018 19:47:47 GMT

I can't reload the qvw you attached, so this is a guess:

replace(Text(Reference),'.000000','') as Reference

Re: How can I replicate Text to Columns from Excel in Qlik script?

cliff_clayman — Mon, 24 Sep 2018 19:55:19 GMT

I still get the Scientific Notation values when I try that.