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.

How to create a automated generating rows within a table

vpalani
5 - Atom

date             region   value%

2021-04-01  East       45

2021-04-01  West       55

2021-04-01  North     65

2021-04-01  South    75


I want to add rows below the table, as shown below without manually entering it


2021-05-01 East null

2021-05-01 West null

2021-05-01 North null

2021-05-01 South null

 

2021-06-01 East null

2021-06-01 West null

2021-06-01 North null

2021-06-01 South null


I need to do this because a quarter is of 3 months and we consider only first day and first month of a quarter.

Any help is much appreciated

8 REPLIES 8
Jean-Balteryx
16 - Nebula
16 - Nebula

Hi @vpalani ,

 

How far do you need to generate this data ? Till the end of the quarter ?

vpalani
5 - Atom

I need this to be generated for each quarter...
For example :
When we are in current Q3, we need Q2 data.
Our data currently has values like;
2021-04-01 (comprising of all month (April, May, June) considered as Q2)
2021-01-01  (comprising of all month (Jan, Feb, March) considered as Q1)

So let's consider Q2, I need to create rows automatically for Months May and June with null as value across each region 

So for every Quarter I need to create extra rows as shown below;

2021-05-01 East null

2021-05-01 West null

2021-05-01 North null

2021-05-01 South null

 

2021-06-01 East null

2021-06-01 West null

2021-06-01 North null

2021-06-01 South null

I hope this helps you

dfurlow
8 - Asteroid

Depending on your answer to @Jean-Balteryx 's question you maybe can use the formula tool with the DateAdd function to generate additional columns for 30, 60, or/and 90 days into the future and then use the transpose tool to get data back into your tall format...?

Jean-Balteryx
16 - Nebula
16 - Nebula

@vpalani , can you share a sample of your data with dummy values except for dates please ?

vpalani
5 - Atom

I have attached it

Jean-Balteryx
16 - Nebula
16 - Nebula

You attached pictures, can you send an Excel file ?

vpalani
5 - Atom

Attached the excel

Jean-Balteryx
16 - Nebula
16 - Nebula

Here is how you can do it ! Tell me if it suits your need or not !

Labels