community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

New iteration

Inactive User
Not applicable

I have data of millions of rows with 1000s columns. Every time I have to extract data, first row will be same but after that onwards extracted data starting point will shift to right

For eg :-

 

Coulmn1   col2    col3    col4    col5     col6

A                 1        2         3         4          5

B                  6       7         8         9         10

C                 11     15       12      13         14

 

output 

 

Column1   col2    col3    col4   

A                 1         2        3

B                 7         8        9

C                12        13      14

 

Please guide me about movement of data.

 

Thanks!

ACE Emeritus
ACE Emeritus

Hi @Inactive User

 

One point to keep in mind is that, if each new row represent an additional shift of the data over, then by the time your output RowCount equals your input ColumnCount, you will have shifted all columns over.

 

An easy way to do this is with R and a couple for loops (which aren't as inefficient as they used to be, but perhaps that's an easy way to do it with an apply variant).  Anyway, sending your dataset into an R tool with the code:

 

dfIn <- read.Alteryx("#1", mode="data.frame")
minRC = min(nrow(dfIn),ncol(dfIn))
dfOut = dfIn[1:minRC,1:minRC]
for (i in 2:minRC) {
  for (j in 2:minRC) {
    if ((i+j) <= minRC) {
      dfOut[i,j] = dfIn[i,i+j]
    } else {
      dfOut[i,j] = NA
    }
  }
}
write.Alteryx(dfOut, 1)

... should work.  This will also illustrate  the "more rows than columns" concern with shifting mentioned above.

 

I've also attached a workflow which illustrates it.  You can adjust the number of rows or columns of sample data, e.g. to assess performance for many rows/columns:

 

scr2.png

 

Inactive User
Not applicable

Thanks for sharing this but this is excluding last column from output.

ACE Emeritus
ACE Emeritus

You should be able to adjust the values in the loops in order to investigate how the output changes. Basically, 

 

i in 1:minRC
i in 1:minRC-1
i in 2:minRC-1

(same for the j loop... this should generate lots of variations.

Hope that helps!

John

Inactive User
Not applicable

Thanks

Labels