topic Character to Number format conversions in qlikview in QlikView

Character to Number format conversions in qlikview

Anonymous — Mon, 20 Nov 2017 11:36:21 GMT

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?

Re: Character to Number format conversions in qlikview

sunny_talwar — Mon, 20 Nov 2017 11:38:38 GMT

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?

Re: Character to Number format conversions in qlikview

Anonymous — Mon, 20 Nov 2017 11:51:27 GMT

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,

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.

Re: Character to Number format conversions in qlikview

sunny_talwar — Mon, 20 Nov 2017 11:55:35 GMT

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,

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;

Re: Character to Number format conversions in qlikview

Anonymous — Mon, 20 Nov 2017 12:00:40 GMT

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.

Re: Character to Number format conversions in qlikview

sunny_talwar — Mon, 20 Nov 2017 12:05:11 GMT

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