Need to Sort and Flag the data using In-DB tools

[VijaySanthoshKumar]

Hi All,

My issue is, i connect to Amazon RedShift Server to extract the data for some analysis. For which i use In-DB tools for reading and writing the data for faster performance. In one of the task i need to do is to Sort the data by Serial Numbers (attached sample data for reference) an by Difference. But i am unable to sort using In DB tools. One way to sort using In Data Base tool is 'Sample' where i have an option to choose the Number of records by using 'Percent' 100 then i can extract and sort all the records, but once i connect to the workflow, i do not see the option Percent, i could see only 'Numbers' and yes it is a live connection. Is there a way to Sort using any other tools that are within In DB tool itself?

Another issue is, I use multi Row formula for flagging, where the Row 1: Serial Number = Row -1: Serial Number then +1 and keep adding the numbers else 1

Actual Formula Used: "IF [Row-1:Serial Number] == [Serial Number]
THEN [Row-1:flag]+1
ELSE 1
ENDIF"

I need to do the same using In DB tools itself. As the dataset is huge, using the tools that are outside In DB tools takes time to run (more than 26 hours) but if i am able to do the same using In DB tools, the same work can be finished in minutes.

Kindly help me in resolving this issue

[VijaySanthoshKumar · Accepted solution]

I found the solution for my own question. I split the workflow into 2. First one will do the processing and then again using write-in DB tool, i wrote the processed data back into the server. Second workflow will pulling the data which i wrote in the DB, while extracting i sorted and created flag and again wrote again in the DB. 

Reason why i cant do the same in the one workflow is because i need to merge 3 different tables from the server and then i have to do some processing after that i need to sort and flag.

Hope this helps to someone...

Thanks & Regards,

Vijay