AutoNumber(RECNO()

[Luben]

has anybody tried AutoNumber(RECNO() with a specific field  to give it unique number.

i tried it as so AutoNumber(RECNO(), BOOKING_NUMBER) as RANK but its so slow and takes so long to load any suggestions or another way Ranking each field something similar to Rank() in SQL.

[JuanGerardo]

Hi @Luben, why are you using RecNo() + anything? RecNo() is unique for every row (usually, or you can use RowNo() instead), so no need to add another field to a ranking index.

Or maybe FieldIndex(https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Inter... ) function is what you are looking for?

JG

[Luben]

hello JG thanks for your response, 

this is what am trying to archive. when  I use AutoNumber(RECNO(), BOOKING_NUMBER) as RANK. I get this result

as you can see in the above, I am trying to exclude bookings that have multiple entry to exclude either the export or import using  the created field Rank.

when is use 

its comes out like this 

 and would not be able to do my filter to exclude bookings with multiple legs i.e. import/export if am trying to exclude either import or export a specific booking

Thanks for your input JG

[JordyWegman · Accepted solution]

Hi Luben,

The reason why it's giving you the same, is because you tell that to the script.

This is what you have now:

Load
    BOOKING_NUMBER,
    RecNo(),
    BOOKING_NUMBER AS RANK
FROM [YourSource];

RecNo() isn't a part of your RANK formula. You were just renaming the existing BOOKING_NUMBER field. If you want RecNo() (maybe better use RowNo() ), you need to give it a name:

Load
    BOOKING_NUMBER,
    RowNo() as RANK,
FROM [YourSource];

But coming back to your needed solution. You want to exclude all doubles, either IMPORT or EXPORT. This how you can do it:

// First load the normal table
Bookings:
Load
    BOOKING_NUMBER,
    TS_DIRECTION,
FROM[YourSource]
;

// Then resident load it because we want to order this load
// I've put asc (Ascending) on both, so when there are two, EXPORT will be first because of the alphabetic order
Final:
Load
    BOOKING_NUMBER,
    TS_DIRECTION,
    FILTER
// With this where filter we only filter the 'new' BOOKING_NUMBER rows.
Where Filter = 1
;
Load
    BOOKING_NUMBER,
    TS_DIRECTION,
    // This will check if the number already exists, if it's new it gets a 1 else a 0
    IF( Previous( BOOKING_NUMBER ) <> BOOKING_NUMBER, 
        1,
        0
    )                                                  as FILTER
;
Load
    BOOKING_NUMBER,
    TS_DIRECTION,
Resident [YourSource]
Order by BOOKING_NUMBER asc, TS_DIRECTION asc
;

Jordy

Climber

[Luben]

hello Jordy,

thanks for your information I tried it below is the result VS AutoNumber(RECNO(), BOOKING_NUMBER) as RANK 

for the bookings above some work with your recommendation and some do not. But when i use AutoNumber(RECNO(), BOOKING_NUMBER) it work exactly how i want it but it takes so long about 1 hrs plus to load on 300 plus records and my date set has over 5 million records. Any other suggestions thanks.

Much appreciated 

Lu

[marcus_sommer · Accepted solution]

I think the suggestion from Jordy by using interrecord-functions showed in the right direction. You may need here and there some adjustments - for example by the sorting which is here on booking-number and direction and didn't regard the recno. Depending on your real case you may also need one or two more conditions to assign the proper ranking-value and/or using the combination of previous() and peek() and filtering a step afterwards to look on the data inclusive recno/rowno and the ranking if the machting-logic is always like expected.

Beside this if your aim is really just to exclude the second and further occurrences of a booking-number you may not need such approach else the following might be already sufficient:

load * from source where not exists(BOOKING_NUMBER);

- Marcus

[Luben]

Thank you guys for your suggestions I would go Implement changes to my script.