idea Re: Reverse Autonumber function in Suggest an Idea

Reverse Autonumber function

simonaubert — Wed, 02 Dec 2020 11:13:39 GMT

Hello all,

The idea is simple : a function that allows a reverse of the Autonumber function. As a reminder, autonumber is used to transform a text into a sequential integer for performance reason (hint : it's linked to the reload so it's not deterministic).

We use it a lot for temporary table but every time we loose time to reverse it. So a function that gets the text back would be huge.

Best regards,

Simon

Re: Reverse Autonumber function

marcus_sommer — Wed, 02 Dec 2020 12:30:18 GMT

Sorry, but I could not vote for this idea - autonumber() is a pretty cool feature but it's not a very smart function else it just dropped the values from a field - they are substituted by the pointer to the data-table. Therefore it couldn't be (simply) reversed. Further this feature is aimed for the final datamodel and not for the more or less temporary ETL steps before. Using it there doesn't simplified the workflow and/or increased the performance - rather the opposite will happens ...

- Marcus

Re: Reverse Autonumber function

simonaubert — Wed, 02 Dec 2020 13:30:34 GMT

Hello @marcus_sommer

I just get something between 30 and 80% increase of performance by using it in ETL steps. It's better for join, applymap, aggregation, where clause etc, etc... The only downfall is that I have to reverse it at the end. This is really strange you don't have the same gain ?

Edit : as a precision, i have something at least millions or rows per table, often hundred of millions.

Best regards,

Simon

Re: Reverse Autonumber function

marcus_sommer — Wed, 02 Dec 2020 14:08:31 GMT

I never made a particular performance measuring for "join, applymap, aggregation, where clause ..." with numeric (and especially not with autonumber-values) values against string-values. Of course the processing of strings is more expensive as the processing of numbers but such an increase of performance is quite surprising to me.

Maybe it's caused through the fact that mostly not my CPU is the biggest bottleneck else the storage/network. Further nearly everything what is performance-intense is done with incremental logics - within multiple layers - with where exists() clauses by avoiding joins and aggregations (at least by larger datasets). What's heavily in use is mapping - often with rather small mapping-tables and if they become larger my lookup-value is mostly a number - either from origin or numeric created keys like:

([StoreID] * pow(10, 4)) + [OrderID] * pow(10, 8)) + [PosID]

I think quite often could the keys be replaced by such a numeric logic or anything similar ones. In regard to your workflow I could imagine that you could substitute the autonumber() with numeric ID's which could be mathematically reversed and/or mapped with string-values whereby this not in each ETL step else just by the final use of the data.

- Marcus

Re: Reverse Autonumber function

simonaubert — Wed, 02 Dec 2020 14:47:35 GMT

We have some difficulties with numeric id, which would be, indeed, the best solution :

-a lot of source system, with no numeric iD shared
-even the few systems where we have numeric ID, the number is too long to be interpretated by Qlik as an integer.

As of now, we haven't found a solution that's not too much expensive.

Re: Reverse Autonumber function

marcus_sommer — Wed, 02 Dec 2020 16:41:22 GMT

Yes, that the length of a number is limited (15 digits without any processing - means an association or a join would be possible and 14 digits with a processing like any calculation or a mapping) is really a hard restriction. But for some ID's it might be enough to ensure unique values.

That your data doesn't contain a numeric ID mustn't mandatory mean that you couldn't create ones. Maybe you could apply logics like the following:

NumericID: load Number, String from NumericID.qvd (qvd);
concatenate load distinct rowno() as Number, String from Fact where not exists(String);
store NumericID into NumericID.qvd (qvd);

Map: mapping load String, Number from NumericID.qvd (qvd);

Fact: load F1, F2, F3, applymap('Map', String, '#NV') as Number from Fact;

Of course it adds some overhead but probably not more as you already have with your reverse-mapping. Such kind of ID generating should get an own generator-layer to keep the logics as small and clear as possible especially as you would probably combine multiple sources to create a certain ID. With a bit luck you may save a lot of these efforts if you could transfer these tasks into your source-database - in general it should be possible to do it there without touching the source-tables (unless reading them) and creating extra tables for it.

ps. somehow it looked like an own created autonumber.

- Marcus

Re: Reverse Autonumber function - Status changed to: Open - Collecting Feedback

Ian_Crosland — Tue, 11 Jan 2022 13:56:43 GMT

From now on, please track this idea from the Ideation por...

Meghann_MacDonald — Wed, 02 Aug 2023 16:22:19 GMT

From now on, please track this idea from the Ideation portal.

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.

Re: Reverse Autonumber function - Status changed to: Closed - Archived

Ideation — Wed, 02 Aug 2023 16:22:21 GMT