Character to Number format conversions in qlikview

[Anonymous]

Hi,

  I have an field which is being fetched from the DB using the format TO_CHAR(A). It will have 15 digits value like 328956000000532 randomly but in an String format because of the TO_CHAR Function. The problem is that now i need to get the first four digits of the field A which i have done through the function Left(A,4). Through this i got the first four digits separately. But now the problem is that i need to put another function which is an ApplyMap('XMapping',Left(A,4)) which is not happening as i am getting an error Syntax Error
: Missing\Misplaced From  . Without this Apply Map the dashboard is running fine. Any inputs on what could be the issue?

My guess is that since it is convert to Character through sql query it is not being able to map with the mapping table where the values are present as Number only.

P.S : I have used the Num Function to convert the TO_CHAR value to an number but still getting the same issue. The value when put in an list box comes as Numeric only. is there any way to know whether the num function has applied correctly and the value is coming as Number format only?

[sunny_talwar]

Can you share the exact script you are using, because the ApplyMap script itself doesn't seem to be incorrect. May be something else is missing, like the XMapping Table?

[Anonymous]

Hi Sunny,

  I am not supposed to share the exact script so i am giving an overview of how my code will look.

XMapping:

Mapping Load

A as X,

B

FROM XXX.qvd;

Temp Table:

LOAD

TO_CHAR(ID) as [Identity Number],

Num(left([TO_CHAR(ID),4),'####') as X

FROM YYY.qvd;

then again after some calculations

Final:

LOAD *,

ApplyMap('XMapping',X,null()) as[ License Plate Umbrella DC]

Resident Temp Table;

All the required mapping Tables is present and there is no wrong in the Mapping syntax also because i have one more mapping column and it runs perfectly the only problem i think is in the conversion to number.

[sunny_talwar]

Everything here looks good to me, except a closing square bracket (which you might have missed it here since this is just a sample script and not the original script). Also, I assume TO_CHAR(ID) is a sql generarted field which probably was not renamed, because QlikView doesn't have a TO_CHAR function

XMapping:

Mapping Load

A as X,

B

FROM XXX.qvd;

Temp Table:

LOAD

TO_CHAR(ID) as [Identity Number],

Num(left([TO_CHAR(ID)],4),'####') as X

FROM YYY.qvd;

then again after some calculations

Final:

LOAD *,

ApplyMap('XMapping',X,null()) as[ License Plate Umbrella DC]

Resident Temp Table;

[Anonymous]

Hi Sunny,

    Thanks for your reply. Yes both are correct. I had only missed the braces and TO_CHAR is done at sql generated field only.

[sunny_talwar]

Don't see anything in the logic you have above... so won't really be able to comment much