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

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

Capture1.PNG

Capture2.PNG

Developer Tools

Accepted answers

All comments

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..

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703