Max Sequence number based on value in table

[Trip]

I apologize if this is a duplicate post.  I tried posting this earlier but it did not look like it posted properly.  Maybe it did but I cannot tell.

What I need to do is look at a table that has a record number, find out what it is if it exists, then use that number to start a new CSV file counting off from what the current number is.

For example: 

if the table has 300 records, I want to start my CSV at 301.

if the table does not have any records, I want to start my CSV at 1.

I have been playing around with the multi-row formula but don't seem to have it right yet.

Any help would be greatly appreciated!

Thanks,

Randy

[Jean-Balteryx]

Hi @Trip ,

If I understood correctly, you'd need to find the max value using a Summarize tool then generate new rows using Generate Rows tool starting from max value.

[Jonathan-Sherman]

Hi @Trip,

You could take the approach of using a Count Records tool to count the number of record in your first CSV file. If there are three records this will return 3, if no records it would return 0. Appending this to your main CSV table you can add the number of records to the record id of your normal table.

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

[Trip]

Yes

[Jonathan-Sherman]

@Jean-Balteryx I would avoid using a summarise. The summarise tool needs at least one record to produce an output (i.e you can't get a count rows of 0 whereas you can in the count records tool.

Summarise tool:

Count records tool:

Kind regards,

Jonathan

[Jean-Balteryx]

You are totally right @Jonathan-Sherman, thank you for pointing it out !

[AngelosPachis]

Hi @Trip ,

If I understand your question correctly, you can use a summarize tool after your first file and then append that to your new table, where you will have created a separate record ID spanning from 1 till N. Then you can add the two to get a new record ID column, starting from your previous table max ID +1.

It get's tricky if there are no rows in your first table, because your summarize tool will then return no rows. This is why you should add a count records tool.

Hope that helps,

Angelos

[Trip]

It is working well except when there is no data returned from my SQL query that feeds the first node.  If a select statement that feeds it does not return anything, is there a way to substitute data?

[atcodedog05]

Hi @Trip 

Based on your previous requirement you can do something like.

1. Label the SQL data and Sub Data

2. Union both data so that they go out in the same steam.

3. Append SQL row count to data.

4. Create a flag column is SQL rows>0 then select SQL data or else Sub data.

5. Based on the flag. SQL data is filtered if SQL data row count is not 0 if not Sub data is selected.

Hope this helps : )

[Trip]

I was having trouble getting it to work so I just put the query in the data connection properties.

IF EXISTS (SELECT COUNT(*)
FROM Table 
WHERE RUN_GROUP = 'X')
SELECT COUNT(*)
FROM Table
WHERE RUN_GROUP = 'X'
ELSE SELECT 0 AS COUNT