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!
Solved! Go to Solution.
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:
Thanks for sharing this but this is excluding last column from output.
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
Thanks