Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

GB and MB word at the end in qlikview

I have data like this 

 

Usage
3.45 GB
45.4 MB
... and so on 

 

i remove GB and MB and only pick numeric words from this column in script and use expression in table  like this 

 

Sum({<MailBoxUsageMonth={'$(vMaxMonthMailBoxSize )'}>}Usage)

 

but in table for display i have to write GB and MB just like in data .. 

What should i do 

 

SubField(left(Usage, Index(Usage, 'B')),' ',1) as Usage

table

 

capriconuser_1-1629465423159.png

 

 

 

6 Replies
marcus_sommer

I think I would keep the information and just calculating with the resolved numeric value, for example within a dual(), like:

dual(Usage,
   keepchar(Usage, '0123456789.') * pow(1024, wildmatch(Usage, '*KB', '*MB', '*GB'))) as Usage

- Marcus

capriconuser
Creator
Creator
Author

@marcus_sommer  i want to change the expression 

capriconuser
Creator
Creator
Author

@marcus_sommer  i tried your solution but this did not give me expected result .. my expected result is 

2.494 GB

37.39 MB

... AND SO ON 

 

result when i tried your solution .. 

capriconuser_0-1629524612765.png

 

marcus_sommer

You need to use the same unit for all calculations and not mixing them in any way. Ideally would be to keep it as small as possible - means converting everything to byte. How it might be done with the raw-data loading is shown above.

The displayed results of your expression could you adjust in a similar way, for example with something like this:

num(YourExpression /
   pow(1024, pick(ceil(len(YourExpression)/3), 0, 1, 2, 3)),
   '#.##0,0 ' & pick(ceil(len(YourExpression)/3), '', 'K', 'M', 'G') & 'Byte')

- Marcus

capriconuser
Creator
Creator
Author

this does not give me required result... please share sample file with your working or may b  unable to get it your solution

marcus_sommer

Did you following all suggestions - means adjusting all appropriate raw-data columns during the load as well as applying the converting + formatting example in regard to your calculations?

Currently I haven't an example which I could share and if it wouldn't be more useful as my suggestions above because they would need to be adjusted, too.

If you couldn't comprehend the logic in detail it's often helpful to split it into multiple measures to see what happens and afterwards you could merge them again. Also useful is to play with such things within a reduced or maybe dummy data-set because the testing of various aspects would go much faster.

- Marcus