how to define dynamic range when I export the query data to Excel template file

[mingjueca]

I have one question about setting dynamic range when I export the query data to Excel template file. 

Here is background : 

I create weekly sale by Rep report by pulling query from Oracle and export the result to Excel template file, then rename the template file with week number. 

For example : current week = 08, the final weekly report only shows sales data from wk1-wk8.  

In the template file, , the header of the tab "data"f has 52 columns for W1-W52. Also the header start from A3. 

Each week the query has one more week data and the number of sales Rep changes too.  

Currently I use the fixed range for example : A4-BB4000 which I choose max week and max sales Rep) instead of dynamic range. 

But I don't how to delete the extra columns from wk9-wk52. 

In Excel VBA, it is easy to remove extra week number columns by adding current week number.

Is there a way to achieve this in Alteryx ?  Thanks all

Attached is the excel file which the first sheet is final report should look like and the second sheet is output I used fixed range.

[Qiu]

@mingjueca 
Can you output the  range A3-BB4000 with the header information?

[binu_acs]

@mingjueca based on my under standing you need to dynamically remove the columns which are not in use for each run. Attaching a sample

[flying008]

@mingjueca 

Hi, pls see the gif.

1- Today is 2022-02-28, So we get the Current Week Number is 10th.

2- Use the Dynamic Selcet tool, Expression like: [Name]="Sale No" || [Name]="Region" || [WeekName] <= CurrentWeek

3- So you can get the max week column like dynamic range for everyday.

[atcodedog05]

Hi @flying008 

Nice way of showing how to do a specific action. Can you share with us how you created this I would be really helpful 🙂

Thank you for the knowledge share 🙂

[Qiu]

@flying008 
nice one.
Can I ask what tool you are using to make the Gif? 😁

[atcodedog05]

Join the club @Qiu 😀

[atcodedog05]

Hi @mingjueca 

I think the above post has provided you with the solution for your use-case. Let me just summarize it end to end.

Let's say this is your template sheet with all weeks. And now you want this sheet to have weeks till W8 and reset removed.

Workflow: You can use the data cleanse tool remove null columns to remove the extra columns. @binu_acs workflow is doing a similar thing.

Writing to file: as @Qiu mentioned you can write from A3- with the headers this way only the required table will be outputted.

Output:

Hope this helps : )

[flying008]

Hi, @Qiu & @atcodedog05 ,

@Qiu My dear friends, maybe you can try to made gif by oCam software.😁

@atcodedog05, Dear, the Cleansing resolved the case is a good idea.👍

[Qiu]

@flying008 
Thank you for the information.
let me give a try!