Hi Guys
I have a data which I want to arrange in a sequential format for the whole year. I am attaching the input file and the desired output. If anyone can help me to build the model to get the desired output.
Solved! Go to Solution.
Hi @sourabmehta2006,
You can Generate Rows for each possible date, then Join in the share volume for each before sorting into your desired output format:
Sorry, I'm still not sure what you're trying to do.
You noted = "The output should be such that it should pull the entire data for 1 cusip for the whole year and then again repeat the same for next cusip"
Are you saying you want to group by the CUSIP, then sum (or count) the Shares?
Or are you saying you want to add a row for every day of the month which is missing?
If you want to add rows - you can use the 'Generate Rows' tool under the Prep category.
Or you can use a multi-row formula and the missing dates.
Tell me which and I'll see if I can work up an example for you.
OK. Easily done.
Here's a link to two examples.
You need to use both the Multi-Row tool and then the Generate Rows.
If I have time end of today, maybe I can do this and provide you file.
Here's a workflow for you.
1) Sort
2) Use Formula Tool to create the Max Date field to use when there is not next date in the data.
3) Use Multi-Row to create the 'stop' value for the loop inside the Generate Rows tool. This is the next 'present date'.
4) Use the Generate Rows tool to loop through and add the missing dates
5) Fix the Shares field to show Null for the new imputed dates.
If this does what you need - PLEASE mark this as the solution.