Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
No autogenerate is for example see the sample file once again.
And see the load statement and Sample atachment also.
load Text(Column) as StringCol;
Temp:
LOAD * INLINE [
Column
1234566
1213sdad
sdasd4565
dsd445
efdfd
dsds458
dfdfs885
yytyty555
5555
111
111
];
Regards,
Anand
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
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.
BI Consultant
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
User Autonumber function
it will convert your alphanumeric to numeric
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
do i hav to do this in excel or in edit script?
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.
BI Consultant
Hi,
You have to do this in script.
Same like Miguel Said.
Load
Text(PRODCD) as PRODCD
......
From xyz.
Regards,
Kaushik Solanki
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