overwrite sheet or range...does not remove the changed record so duplicates

[Raj_007]

Hi,

I have an SQL query and i tried multiple options to keep the formatting of the excel file but it still keeps the duplicates in the excel file

what i have done..

1). I used count records to see how many rows are there.. then I add 1 to the count so that it will add NULL record/row

2). I did a right join based on the record ID and the row count 

3). Using the row count to pass to the sheet range

3). used overwrite sheet or range -

4).checked keep formatting

for some reason i still have duplicates in the excel file

attached 2 screenshots... looks like i am missing something

[OTrieger]

@Raj_007 

It is hard to say from what you have shared so far.

Are you writing your data to an empty excel or excel that already containing data?

[Raj_007]

Hi, thanks a lot. I have an excel file with just the column headers in it and formatted. no Data in it

[OTrieger]

@Raj_007 
As we cannot see the data in Alteryx, is the data is duplicated?
How many rows of data do you see in Alteryx and how many do you have in your output files?

[Raj_007]

Hi , when the data changes it keeps both rows - i think it is getting the new row and updating the existing in excel that is causing 2 rows to be exactly same

just high level

say

step 1. i have 13 records coming from my query.. I use the recordID , count records +1 - this will give me 14 rows -after i join (J and R) and union them  14th row is null

step2. i create 3 calculated columns file name, sheet name, path

sheet name like In Progress+tostring(RowCount))  something like In Progress$A1:W14

path = location + file name + sheet name

in the output: overwrite sheet or range - checked the keep/preserve formatting

take file/table name from field = using path

change entire file path

[Raj_007]

in this example: my A1 starts headers so A14 is the last record with data A15th is the null row - do we include null record/row in the sheet range: meaning it should be $A1:W15?

[Raj_007]

since we need 1 for header it is A1: to W15  - let me see if i get the duplicates. as of now i do not have dups because of the data, but when some value changes thats when i am getting the problem of dups

adding 1 extra NULL record is ok to enforce the right join is what i need to test

[Raj_007]

every time there is a change in the data i think thats when i see the 2 exact rows for the same record. 

[cjaneczko]

What changes in the data? Fewer rows are output on the next run? Say the new output is A1:W12, is it A13:W15 that still remain from the last run? Is that the issue you are having?

[Raj_007]

some data values might change for ex: assigned to column which has null changed to xyz and it keeps 2 exact same rows with xyz..