Change of reference of sheets
Team I have a sheet with multiple columns with formula referencing a Joined sheet of sheet1 and sheet2. I have now used two different sheets as input and created a new Joined1 sheet. Now I want the sheet with formulae's to point it to Joined1 without manually changing the reference. can this be done ? I am not familiar with API 's
-
Hello Ganesh.
If the Joined and Joined1 Sheets have the same columns, perhaps instead of creating a new Join Sheet, you could change the existing one? Open the Join dialog for the Sheet Joined and change the source of the join operation. In this case, you might need to change the references for columns you use as join keys, but all the rest should be updated automatically. -
Thanks. But what meant is not on how the join to be updated. let me clarify. I have the following Sheet1, Sheet2 and Join of Sheet1 and Sheet2 as Joined. Sheet 4 has columns from Joined as well as additional columns with formula. Now support I bring in Sheet5 and would like all references in Sheet4 to change to Sheet5. Currently I open sheet4 and manually changing each column references to Sheet5. example original
Column1 in sheet4 = groupby(#Joined!city_flag) post introduction of Sheet5 i would like to avoid opening sheet4 and change as follows groupby(#Sheet5!city_flag). I can rename Joined sheet to Joined1, in that case Sheet4 will have reference of Joined1. if Sheet5 is also renamed as Joined it is not solving the manual work. Another problem i notice is when i change the reference for one column and press apply (tickmark) got a pop up saying Loading data and once the same is done then only i can move the next column. this is quite time consuming.My requirement is avoiding the manual change and simple switch the sheets. also would like to know how to navigate to columns using position numbers. Because workinfo sheet presents column1=, column2 = ...
Please bear with me for the lengthy note and thanks in advance for your help.
-
Hello Ganesh.
Thank you for the detailed description.- A Workbook Sheets and their relations are stored in the database by IDs. The names is the more meaningful way to represent the restructure for a user. This is why you could rename an existing Sheet and see the further references updated - in this case. The ID doesn't change.
- Loading data messages is expected as a lot is going on under the hood at this time, e.g., preview generation.
What is your Datameer version? I believe we have an enhancement request to allow bulk column repointing.
-
7.5 is the version. also any suggestions on how to navigate to columns based on column numbers. for example if i would like to see what are the columns built using a formula , I look at workbook info. Though it provides the column numbers and the formula, not the column name. So it is important to have a feature which enables to provide the column number rather than the name itself. or worksheet info should also list the column names. is this possible is what i would like to know
-
Hello Ganesh.
I'm not sure if I understand what do you mean under a column number. Perhaps you could elaborate?
When you open a Workbook, there are Jump to Column or Sheet and Toggle Columns instruments under the View menu or at the icon bar. They help you to navigate the desired Columns / Sheets or view all columns on the active Sheet. Could these instruments help you to get the information you are looking for?
Regarding the bulk column repointing, we started implementing this feature in Datameer versions 10.1.x and 11.1.x. As the 7.5.x reached the end of its maintenance cycle in May 2020, no new features will be released for this branch. Thereby you would need to upgrade to one of the latest Datameer versions to get this instrument.
-
Hi Konsta,
Thanks for you reply.
In a sheet if I check the workbook info I see a listing of all columns of that sheet. But issue here is we dont get to see the actual names of the columns. Instead it appear as follows:
Column 1 :=Groupby(#Sheet1!Field1)
Column 2:=groupsum(#Sheet1!Field2) etc.,
Column 100: asdate(#Sheet1!Field3;'ddMMMyyyy')
I want to see what is name of column 100 given that I know the transformation on the same. in our versions 7.5.12 the only option is if I know the column name then I can jump to that columns. I case if i dont know the column name and want to jump to the column 100 I cant do it now.
-
Hello Ganesh.
In version 7.5.12, there is no better way to check the details of the columns. However, in the latest versions, we revised this functionality and:
- Removed Workbook Info.
- Added a Sheet tab to the Workbook's Inspector.
At this new Sheet tab, one could view the list of columns on the Sheet, including their names and applied formulas. The search and highlighting options are available as well.
In the recent Datameer versions, we also implemented the bulk column repointing. On only need to change a reference for the first column, and the product offers to update all the subsequent references.
Let me know if you have any further questions.
-
Hi All,
Need your help to debug. I have an existing joined sheet Named as "Joined1" with some reference tables. I wanted to update this join with a newly created tables. When I open the join dialogue and provide the tables and columns for join the following message appear. I have ensured that the data types of join fields are of the same type. Ideally I would expect the update to work fine and subsequent depended sheet will auto update. because of this error message I am forced to change the reference of each columns manually on a formula driven sheets and is time consuming.
please help me debug the following error message.
-
Hello Ganesh.
As far as I can see from the screenshot, the main error is JoinColumnsTypeDoNotMatch. It means that the new Join keys you are using have different types. It would be helpful if you could open the Stacktrace dropdown at the error message window, copy the full error message, and paste it here.I've also run the following test in my 7.5.12
- Create a new Workbook with Sheet1, Sheet2, Sheet3, and Sheet4.
- Each Sheet contains three columns A, B, C.
- Column B at Sheet1 and Sheet2 has BIG_INTEGER data type.
- Column B at Sheet3 and Sheet4 has INTEGER data type.
- Join Sheet1 and Sheet2 by column B.
- Create Sheet5 and reference any column from the Join Sheet.
- Go back to Joined Sheet and open Join dialog.
- Remove existing config and join Sheet3 and Sheet4 by column B.
- Joined Sheet is updated without any issue.
- References at Sheet5 are broken because the Joined Sheet column names changed.
At the moment, this is the expected behavior considering the way how Datameer handles updates of existing Join Sheets and subsequent references.
If you explain what problem you are trying to address by changing the logic of the Join Sheet in the given Workbook, I could search for a better solution.
Please sign in to leave a comment.
Comments
13 comments