topic How to repeat charcaters while loading as Excel file in QlikView

How to repeat charcaters while loading as Excel file

madhubabum — Thu, 08 Oct 2015 07:20:21 GMT

Hi Experts

I am having Emp_Id field (Apply logic to repeat characters)

Logic is applied in qlikview , If i am storing as an Excel file it is not applying logic on Excel (I don't want any changes in excel)

Made changes on Qlikview Script (Not in Excel )......

How we can achieve this one ?

Here i am attaching sample Excel file and Application

Thanks

Madhu

Re: How to repeat charcaters while loading as Excel file

petter — Thu, 08 Oct 2015 08:02:52 GMT

You can prepend a space character to the Emp_Id in your load script:

But you can't use the regular SPACE character with ASCII code 32 since Excel will just ignore that and treat the rest as a number anyway. But there are other SPACE characters in the ASCII, ANSI and UNICODE character set that both QlikView and Excel do handle but does not make assumptions about.

So you could use either:

Chr(160) which is so called no-break space in ANSI / ISO character sets

Chr(8203) which is zero-width space in Unicode

Both breaks Excel's habit of trying to interpret them as numbers and will leave a space in front of them and essentially keeping them as text.

If you need the front-padding to be zero you obviously use the Unicode 8203 character.

Emp:
LOAD 
  // Leaves an "invisible" space (zero-width) in front of the text string Emp_Id...
     Chr(8203) & Text((repeat('0',9-len(Emp_Id)))&Emp_Id)  as Emp_Id
     // This can be used if it is no big deal if the font-padding is one space character... 
     //Chr(160) & Text((repeat('0',9-len(Emp_Id)))&Emp_Id)  as Emp_Id
FROM
[Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

STORE Emp into Emp.csv (txt);

Re: How to repeat charcaters while loading as Excel file

petter — Thu, 08 Oct 2015 08:03:40 GMT

Re: How to repeat charcaters while loading as Excel file

madhubabum — Thu, 08 Oct 2015 08:17:51 GMT

Hi Petter Skjolden

It's working fine

Thanks a lot

Madhu