Reduce size of field to reduce memory

[sifatnabil]

Hi,

I have a field in the Oracle database that's a UUID (Data type=RAW (16 BYTE),) which is 32 characters, e.g. 30363030313435463336334439434645

Even though it's 16 bytes, Qlikview probably converts this to a string, and this takes up a huge amount of memory in Qlikview at 38 bytes per unique value. Is there ANY way to reduce the size of this data type on the Qlikview side?

I use this field in front-end calculations which really slows down the document.

[Gysbert_Wassenaar]

Perhaps you can use the autonumber function: autonumber(UUID) as _UUIDno.

[marcus_sommer]

If it's only a key you could use the suggestion from Gysbert. If you need these field-values within the gui you could try to split these field into two or more fields, maybe something like this:

...

num(mid(UUID, 1, 16)) as UUID_1,

num(mid(UUID, 17)) as UUID_2

...

Maybe there are some logic within the UUID which could be extracted and separat stored and which will reduce the amount of unique values.

- Marcus

[Gabriel]

Hi,

What data type is this field from Oracle (Oracle database that's a UUID).?

[sifatnabil]

Thanks Gysbert Wassenaar, I tried this and it does reduce the size of the field significantly to 1 byte per value, however the graphs in the front end using this autonumbered field are still slow. The field in question is called ORDER_ID, and I am using it on graphs with calculations like these:

count(if(wildmatch(USERNAME,'*mm*')=0 and (TOTAL_AMOUNT<>0 or COMPLETED_AMOUNT<>0),ORDER_ID))

I also have them in variables, which are called in graphs like this:

=num($(eovolume)

/

$(totalovolume),'#.#%')

What do you think is the root cause of the slowness then if not the field size?

[marcus_sommer]

The calculation should be faster if you used set analysis instead of if. I'm not sure if this expression is correct for you, but you could it use as starting point:

count({< USERNAME -= {'*mm*'}, TOTAL_AMOUNT -= {0} + COMPLETED_AMOUNT -= {0}>} ORDER_ID)

Another point could be to change/optimize the datamodel to avoid unnecessary hoping between tables. It won't help in each way but it's worth to think about: http://community.qlik.com/blogs/qlikviewdesignblog/2015/01/19/number-of-hops

- Marcus

[sifatnabil]

Thanks Marcus_Sommer I am converting these to set analysis now and seeing if this works. However while your expression looks correct to me, it's giving an error "Error in set modifier expression". Any ideas? It does follow the correct syntax  so not sure why this would happen. Any ideas?

[marcus_sommer]

Unfortunately the syntax-check from editor worked only for relative simple expressions. If the expressions are more complex (especially with set analysis "-=" or variables) or the expression is quite long the editor returned often wrong messages false and positive ones. If the expression returned your expected result you could ignore these messages.

- Marcus

[sifatnabil]

Hi Marcus_Sommer , nothing is returned in the expression unfortunately. This means there's actually something wrong with the set analysis, but I can't figure out what.

[marcus_sommer]

It's quite difficult to say what could be wrong but I would assume it's the or-condition between the *_AMOUNT fields. I suggest you split the set analysis parts to see which worked and returned the expected result and then adds them together again - step by step.

count({< USERNAME -= {'*mm*'}>} ORDER_ID)

count({< TOTAL_AMOUNT -= {0} + COMPLETED_AMOUNT -= {0}>} ORDER_ID)

...

Also you could modify the set analysis statements, for example:

count({< USERNAME = e({< USERNAME = {'*mm*'} >} USERNAME) >} ORDER_ID)

count({< TOTAL_AMOUNT = {">0<0"} + COMPLETED_AMOUNT = {">0<0"}>} ORDER_ID)

- Marcus