Forum Discussion
Modify table tricks needed
Alright, give this a try:
I begin with this table:
Create a duplicate of this table, now we have Table 1 & Table 2.
In Table 1, filter reason for call on "Loading":
In Table 1, highlight [Trip number] and [Unit Number] then press "Group By". Use the same setting as the picture below:
Now we move onto Table 2. Filter the table on discharging then do anoth "Group By" with these settings:
Finally we want to merge the two tables. Press "Merge as New" then use the following settings (Ctrl click to highlight more columns):
Finally expand the "End" column:
Should give you the following result, which i hope is correct:
Br,
JHappy to hear! I can actually help you out with that 😉
Br,
J
Hei, yes, this is almost what i want. I just need one row for a unique Trip number and remove the three columns in the middle...
However, you syntax does not work on my pc. I changed my table name to Table (2), as I guess that is your table name and in Data - > Modelling -> Add a new column and put in your line into the formula area... but i got error message... is it becuase my dates have hierachy? but I cant remove it...
Super Usertex628 , Please help. Anonymous , share error.
dont worry. let me try it first... i think i have a clue... the computer setting, yours is ; and mine is ,
- tex628
Community Champion
The "," and ";" are called delimiters, they are related to your region. In Europe we use ";" and in the US it's ",". If it still doesn't work send me a picture of the error and we'll see what we can do! 🙂
As for removing the columns and making the table distinct we'll take a look at that when you get the first two columns sorted.
Br,
J
Hi,
Now I am able to make the two extra colomns Start and End in the Data tab. However, since I need to remove the three columns in the middle and duplicated rows, I need to go to the Data Query and do that. But once in Data Query, the two new calculated columns are not there. Why and what I can do?
- tex628
The reason that the two columns are not present is that they are calculated columns, they depend on the datamodel as it is when it's loaded and they cant actually exist before the load!
What we want to do instead is write a table statement, i'd say we want to do something like this:New Table = SUMMARIZE( 'Table (2)' ; [Trip Number] ; "Start" ; MIN([Start]) ; "End" ; MAX([End]))
Let me know how it goes!
Br,
JHi, Sorry I dont quite understand what you meant, i just want these calculated columns appear in Query Editor... What can I do to achieve that?
But I did make the table you recommended... However, it, also, does not show up in Query Editor...
What should I do if I want to be able to do some editing in Query Editor?