field with number and alphanum source how to format it to string in edit script

[rotero]

hi,

i have this problem

an excel file field name PRODCD but with value of alphanum

if the value of this field contains numeric, automatically excel expect it to be numeric, so if i have other  record with alphanum it will create confusion in loading.

My question is how do i convert it during loading (in edit script) because i have a table with the same field name so that i can get full product name when i reload.

[its_anandrjs]

Hi,

If you want to convert a alphanum to string try to convert a alphanum column into string by purgechar(alpha,0123456789) as Stringcol

Regards,

Anand

[Miguel_Angel_Baeyens]

Hi,

To keep all values under that field as literals (strings) use the Text() function

Table:
LOAD Field1,
     Text(Field2) AS Field2,
...

That means that for example, "0000001" is not equal to "1" forcing QlikView to interpret that value as a string, so al leading zeroes are considered as strings.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[its_anandrjs]

Hi,

Or you need to try this syntax to convert Alpha to string like

=text(Alpha) as StringColumn  in load script.

By this code you convert

1. Number to String   =Text(NumCol)

2. Alpha to String  =Text(AlphaCol)

See the solution sheet in Attached sample.

Regards,

Anand

[SunilChauhan]

User Autonumber function

it will convert your alphanumeric to numeric

[kaushiknsolanki]

Hi,

   What i understood is, you want to convert the PRODCD in to string, even if it contains only the numberic data?

   If yes then you should use the text() function of qlikview to convert the data type of field to String.

   Syntax : Text(Field_Name) as New_Field_Name

Regards,

Kaushik Solanki

[rotero]

do i hav to do this in excel or in edit script?

[Miguel_Angel_Baeyens]

If you do that, you will get rid of all the characters from 0 to 9 from your field. Is that what you are looking for? I'm assuming that you do want to keep those chars, but interpret them as strings instead of an actual figure. In any case, you need to do that in the script, as shown below.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[kaushiknsolanki]

Hi,

    You have to do this in script.

    Same like Miguel Said.

    Load

        Text(PRODCD) as PRODCD

        ......

    From xyz.

Regards,

Kaushik Solanki

[its_anandrjs]

Hi,

You need to do this in script load statement as well as if you want it you do this in object level also

See the load script code.

Temp:

LOAD

'123455'  as NumCol,

'dddddsddss1253454' as AlphaCol

AutoGenerate 1;

load

*,

Text(AlphaCol) as StrCol,

Text(NumCol) as StrCol2

Resident Temp;

Drop table Temp;

See the attached sample also.

Regards,

Anand