topic Re: New to QlikView in QlikView

New to QlikView

roee1983 — Sun, 08 Sep 2013 09:52:56 GMT

Hi all,

I'm trying to do LEFT JOIN between 2 tables and when i'm trying to reload it is getting stuck.

my key fields are company code number and i think they have difrent format.

how do i make it in the same format?

thanks

Roee

Re: New to QlikView

ariel_klien — Sun, 08 Sep 2013 10:51:51 GMT

Hi,

Try Text(company_code_number) as company_code_number.

in each table.

Ariel

Re: New to QlikView

orital81 — Sun, 08 Sep 2013 11:17:59 GMT

In addition to Ariel's answer,

You can also use :

AutoNumberHash128(Company_Code) as Company_Code

This will generate a number out of your company code.

Joins by numbers are faster than joins by text

Re: New to QlikView

Sun, 08 Sep 2013 11:28:26 GMT

Hi Ori,

but think about, functions like autonumber* are performance brakes, often a script run 2 to 3 times faster without autonumber.

Best Regards

Sacho

Re: New to QlikView

orital81 — Sun, 08 Sep 2013 12:00:23 GMT

Hi Sacho,

Thanks for this comment, Actualy, this fact is new to me.

As to my knowledge, Numeric Join is much faster than Textual join

due to the fact that numbers has much less options to scan (0-9 instead of a-z).

Is there something in AutoHash function that takes tiem to calculate? if so - do you suggest AutoNumber instead?

Re: New to QlikView

Nagaraju_KCS — Sun, 08 Sep 2013 13:00:48 GMT

Hi Ori,

what is the use of AutoNumberHash128.

please explain ..

Re: New to QlikView

orital81 — Sun, 08 Sep 2013 13:23:55 GMT

Autohash returns a unique integer value for each distinct value you define.

This is mostly used for joins.

For example:

1. Define Join without AutoHash (using concatenated strings):

* field_A&'-'&field_B AS Key

2. Define Join with Autonumber you can define it this way:

* autonumberhash128(Field_A,Field_B) AS Key

See more in the Qlikview Help:

--------------------------------------------------------------------------------------------------------------------------------------------------
autonumberhash128(expression {, expression})

Calculates a 128-bit hash of the combined input expression values and the returns a unique integer value for each distinct hash value encountered during the script execution. This function can be used e.g. for creating a compact memory representation of a complex key.

Example:

autonumberhash128 ( Region, Year, Month )

Re: New to QlikView

Sun, 08 Sep 2013 15:17:33 GMT

Hi Ori,

you are right, numeric join is much faster than textual. I suggest autonumber as a good solution if there is no need of handling big amount of data daily, 50 million or more of records by example. Long ETL duration is relative, if there are running a lot of tasks on your QV server, one hour loading time could be considered as long. Many applications handle only fact tables with 10 to 20 million of records. In this cases it doesn't matter. If the cardinality of your join key is low, autonumberHash is no problem at all, but joining fact tables with many records and a low cardinality of the join key, calculation time for joining tables is still costly. High cardinality and a lot of records could be critical for autonumberHash due to the complexity of the hash algorithmus. He has to handle collisions of the calculated hex values, this is a mathematical problem of complexity (O(n) etc.).

But there is another aspect for the run time performance of the QV Frontend. I'm sure the associative in memory data model runs really faster if the keys for this in memory tables are numeric. So Selections an Aggregations could be implemented (inverted lists...) faster. The best solution is to test both scenarios.

Hope that helps.

Best regards

Sacho

Re: New to QlikView

orital81 — Mon, 09 Sep 2013 06:21:45 GMT

Hi Sacho

First, thanks for the detailed explanation.

Please clarify - what is low and high cardinality?

Re: New to QlikView

Mon, 09 Sep 2013 06:36:05 GMT

Field values: reg, green, blue => cardinality = 3

Field values: 1,2,3,...,1000000 => cardinality = 1 mio.

Sacho

Re: New to QlikView

er_mohit — Mon, 09 Sep 2013 06:40:33 GMT

try to make composit key like if you have to fields common in both side

then write (upper(company_code) & '|' & commonfield) as Key in both table and try to left join

hope it helps

Re: New to QlikView

orital81 — Mon, 09 Sep 2013 06:43:42 GMT

I understand,

Thanks for the clarification.

Ori