I have a requirement where i have to run a file which has data for multiple countries into one file where sheet names are created for a specific country.
So while writing to an excel writer connected with a group loop start and a variable loop end, it works fine the first time.
Now the issue is that on the second run, the excel writer fails because it has two options, one with regards to excel file and one with sheet. I have selected sheet renaming controlled through the flow variable and selected overwrite option, the file name i have given append option.
So if i do append option on file, the second run doesn’t overwrite the data and sheets which have some other countries data is retained even though i don’t want that.
If i do an overwrite option on file, it just places one country’s data only as in a group loop it keeps on deleting the first file with the second one.
What i want is the group by functionality to work directly on the writer so it creates a fresh file everytime.
1st file has data for Australia, USA, UK, China
2nd file has data for Australia, USA, India, UK
If i do append on file and overwrite on Sheet
it replaces 1st file output data of australia, usa and uk with the second file, but retains china and adds in india.
If i do overwrite on file, it simply shows me UK data.
The only solution i found out was deleting the first file output before running the second file but i want that flexibiility to just replace a file with the same name and not delete for every run.