Find the first date

FEDE — Thu, 06 Mar 2025 12:44:52 GMT

Hello everyone,
In qliksense, In the script editor, I need to select for each order the first date that is followed by at least two other consecutive dates.
I have table like this:

order, date
a,1-gen-2000
a,1-feb-2000
a,1-mar-2000
a,1-apr-2000
b,1-oct-2001
b,1-dec-2001
b,1-gen-2002
b,1-feb-2002
b,1-mar-2002
c,1-jun-2003
c,1-jul-2003
c,1-sep-2003

the result i hexpect is:
order,date
a,1-gen-2000
b,1-dec-2001
c,null

Any suggestion?
Thank you!!!

Re: Find the first date

henrikalmen — Thu, 06 Mar 2025 13:27:20 GMT

I think I would add a flag to the first table using Previous(), and create the result table based on that. Add a column (using join and resident load from the same table) where you check if previous(date) is the previous month - if it is set the flag to 1, otherwise to 0. After this you create a temporary table where you sum the flag field per the order field - all "order" with a value of at least 3 are those that you are looking for. Use those orders to check for min(date) and there's your answer.

Something like that, unforutnately I don't have time right now to write code sample.

left join(table1) load order, date, if(previous(monthstart(date))=monthstart(date) resident table1 order by order, date desc;

topic Find the first date in App Development

Find the first date

Re: Find the first date