Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Luben
Creator
Creator

AutoNumber(RECNO()

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.

 

2 Solutions

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder

View solution in original post

marcus_sommer

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

View solution in original post

6 Replies
JuanGerardo
Partner - Specialist
Partner - Specialist

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
Creator
Creator
Author

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

 

Luben_0-1619294367261.png

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 

Luben_1-1619294812046.png

its comes out like this 

Luben_2-1619294868709.png

 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
Partner - Master
Partner - Master

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

Work smarter, not harder
Luben
Creator
Creator
Author

hello Jordy,

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

Luben_0-1619375426542.png

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

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
Creator
Creator
Author

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