topic Re: Remove Decimals From A Text Dimension in QlikView

Remove Decimals From A Text Dimension

bxprokop — Fri, 15 Nov 2024 23:44:41 GMT

When loaded I have a dimension that is in Text Format. The Dimension has both Text and Numeric values in it. The numeric value (in text format) are listed as 99213.000000. How do I remove the decimals from the numeric value and maintain the values of items that are Text (i.e. G0378)? I have tried the following:

- num(Value, '#####') - This removes the decimals but also blanks out any value that begin with an Alpha i.e. G0378

- floor(Value) - The results are the same as num(Value, '#####')

Thanks in advance for your assistance.

Re: Remove Decimals From A Text Dimension

Kushal_Chawda — Sat, 24 Oct 2020 10:39:08 GMT

@bxprokop try below

=floor(keepchar(Value, '0123456789.'))

Re: Remove Decimals From A Text Dimension

bxprokop — Sat, 24 Oct 2020 10:54:47 GMT

This maintains the values but removes any letter from the value. Example: G0378 becomes 378. I want to maintain the G0378. I tried floor(keepchar(Value,'ABCDEFGHIKLMNOPQRSTUVWXYZ0123456789.')) but this ends in the same result as my original question with anything begin with an Alpha being blanked out. Let me know, if there is another option. Thanks for your help.

Re: Remove Decimals From A Text Dimension

Kushal_Chawda — Sat, 24 Oct 2020 15:33:11 GMT

@bxprokop Provide few sample values with expected output. It will help to build the correct logic

Re: Remove Decimals From A Text Dimension

rajender_qlik — Tue, 27 Oct 2020 22:32:45 GMT

Hello Kush,

This expression returns only the numeric value in the listbox. Even I have listbox which contains alphanumeric such as {xxxxx, MO1727, B4080, 11-5-448, 64-5678;, 4-6-5437, 8673.0000, 1235678.0000}

I want to remove the just decimals after '.' but remain same which are not numbers.

Output : {xxxxx, MO1727, B4080, 11-5-448, 64-5678;, 4-6-5437, 8673, 1235678}

Thanks in Advance.

Re: Remove Decimals From A Text Dimension

Kushal_Chawda — Tue, 27 Oct 2020 23:02:14 GMT

@rajender_qlik below should work.

if(Index(Field,'.'),Left(Field,Index(Field,'.')-1),Field) as New

Re: Remove Decimals From A Text Dimension

rajender_qlik — Tue, 27 Oct 2020 23:09:49 GMT

@Kushal_Chawda Thanks, It worked.