I want to insert few empty rows based on my date columns.

akumar2609

I need help in below 2 query

              a) inserting the few blank rows in my data set based on the date run field.

              b) I want to take out the difference of the total amount based on the date range.

Below are sample data and my requirement:-

Data set

Category	Run time	Product Total
A	20-Nov	10
B	20-Nov	20
C	20-Nov	-30
D	20-Nov	40
F	21-Nov	-20
G	21-Nov	40
H	21-Nov	60
I	21-Nov	30
J	21-Nov	62
A	22-Nov	-10
B	22-Nov	-20
D	22-Nov	-45
F	23-Nov	20
C	23-Nov	45
G	23-Nov	90
H	23-Nov	22

I have the above data set where I have data for four days i.e. 20, 21, 22 and 23 Nov. 

Expected Result

Category	Run time	Total
A	20-Nov	10
B	20-Nov	20
C	20-Nov	-30
D	20-Nov	40
Total		40
Category	Run time	Total
F	21-Nov	-20
G	21-Nov	40
H	21-Nov	60
I	21-Nov	30
J	21-Nov	62
Total		172
Net Balance	132
Category	Run time	Total
A	22-Nov	-10
B	22-Nov	-20
D	22-Nov	-45
Total		-75
Net Balance	97
Category	Run time	Total
F	23-Nov	20
C	23-Nov	45
G	23-Nov	90
H	23-Nov	22
Total		177
Net Balance	102

I need the result in above format where:-

         a) there are few rows gap between all the data set based on the date run

         b) there should be a total column which will add the Product total

         c) there should be a net balance column which is current day total - previous day Net Balance

              for example:- (i) for 20th Nov data run, it should be just the total as it is the first date of the data set

                                    (ii) for 21st Nov data run, Net balance = 172 (21st data total) - 40 (20th Nov day total) = 132,

                                    (iii) for 22nd Nov data run, Net balance = -75 (22nd data total) - 132 (21st Nov day total)= 57 and so on.

Hammad_Rashid

Here’s how you can achieve your requirements in Alteryx:

a) To insert blank rows based on the date run field, you can use the Multi-Row Formula tool. 

• Select the Multi-Row Formula tool from the tool palette and connect it to your input data.
• In the Multi-Row Formula configuration window, select the Run time field as the Grouping Field.
• In the Expression field, enter the following formula: IF FIRST() THEN [Product Total] ELSEIF LAST() THEN [Product Total] ELSEIF NOT ISNULL([Product Total]) THEN [Product Total] ELSEIF ISNULL([Product Total]) THEN "" ENDIF, where Product Total is the field that you want to insert blank rows for.
• Rename the output field to something like Data with Blank Rows.
• Run the workflow to see the output.

b) To take out the difference of the total amount based on the date range, you can use the Summarize tool. 

• Select the Summarize tool from the tool palette and connect it to your input data.
• In the Summarize configuration window, select the Run time field as the Group By field.
• In the Summarize configuration window, select the Product Total field and choose Sum as the Aggregate function.
• Add a new field by clicking the Add Field button and enter the following formula: IF FIRST() THEN [Product Total] ELSEIF NOT FIRST() THEN [Product Total] - PREVIOUS([Product Total]) ENDIF, where Product Total is the field that you want to calculate the difference for.
• Rename the output fields to something like Total and Net Balance.
• Run the workflow to see the output.