Row number based on date

[Jakub_VO]

Hello,

i am struggling with (probably easy to fix) issue.

In the table on my dashboard I want to have new trip number for every new date, if i use RowNO it goes wrong since on 24/04 it should be trip 2. RowNo is probably wrong approach and i sense there is other solution for this but can't figure it out.

[Taoufiq_Zarra]

several approaches ! maye be in script :

Data:
LOAD * INLINE [
    Date
    18-04-2020
    24-04-2020
    24-04-2020
    28-04-2020
    01-05-2020
];

output:
noconcatenate
load *,rowno() as [Trip number];
load  Date(Max(Date),'DD-MM-YYYY') as Date resident Data group by Date;
left join load * resident Data;

drop table Data;

[Anil_Babu_Samineni]

Perhaps short like

LOAD Date, AutoNumber(RowNo(), Date) as RowNo INLINE [
Date
18-04-2020
24-04-2020
24-04-2020
28-04-2020
01-05-2020
];

[whiteymcaces · Accepted solution]

Depending on your data, and how you load new data etc, you could try the AutoNumber() function. It will create a unique integer, in numerical order starting at 1, for each instance of a value that is loaded in. The first row loaded will be allocated the integer 1, then 2 etc, however, when a row is loaded that has the exact value as a previous row, that new row will be allocated the same integer as the previous row. Using your table as an example if the data was loaded in the same order as your table, the Date 18-04-2020 with have 1 as the AutoNumber, the Date 24-04-2020 with have 2 as the AutoNumber etc.

Things to be careful of:-

1. If the data is not loaded in date order, then the AutoNumber assigned to each Date will not be in numerical order, i.e. if 01-05-2020 is loaded first, it will have 1 as the AutoNumber, and if 18-04-2020 is loaded second, it will have 2 as the AutoNumber.

2. Every time Qlik encounters the Date 18-04-2020 in the same Load, it will have the same AutoNumber, irrespective of the row number.

Table:

Load

Date,

AutoNumber(Date) as [Trip Number]

From Source;