Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Arrange output in sequential format for the whole year.

sourabmehta2006
5 - Atom

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.  

 

 

7 REPLIES 7
Aaron_Harter
11 - Bolide

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:

1.PNG 

brendafos
10 - Fireball

Unless I missed something - you just need to use the SORT tool.

 

sourabmehta2006
5 - Atom
Thanks for your reply.

Sort function will not help because if you see the input file you will see
that each security number has some unique date and in the output file I
need those in sequential order.
brendafos
10 - Fireball

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.

sourabmehta2006
5 - Atom
I want to add a row for every day of month which is missing.
brendafos
10 - Fireball

OK.  Easily done.

 

Here's a link to two examples.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Duplicate-Multiple-Rows-for-Missing-Da...

 

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.  

 

 

brendafos
10 - Fireball

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.  

Labels