Assign a name for table as a result of Right Join

[#1]

I have organized the load of my data files in different tabs.

In first tab I load data from an excel file, something like below:

[Orders]:

LOAD

     OrderID,

     OrderType,

     Date,

     Description

FROM

[Path\To\My\Excel\file\Orders.xlsx]

(ooxml, embedded labels, table is Sheet1);

Then in another tab I create a calendar which table name is for example [MyCalendar].

Finally in another tab I perform the join between [Orders] table in tab#1 and the custom calendar created [MyCalendar] like below:

[ResultJoin]:

right join([Orders])

Load *

Resident [MyCalendar];

I want that the name of the table as a result of the right join to be named [ResultJoin] for example, so I have put it above.

Now, in another tab, last tab in my script I perform a load like below:

[FinalTable]:

Load *

Resident [ResultJoin];

The problem is that when loading data it fails saying [ResultJoin] table is not found when the above last script is executed.

Why?

[oknotsen]

A join will keep the name of the first table you loaded, no matter if it is a left, right, inner or outer join.

So in your case the table will be named Orders.

You could add a rename after the right join to fix that if you like:

rename table Orders to ResultJoin;

[#3]

when you join a table the name of table result its a name of the table of the join. if you whant rename your table result you need rename you table of the join example below:

[ResultJoin];

LOAD

     OrderID,

     OrderType,

     Date,

     Description

FROM

[Path\To\My\Excel\file\Orders.xlsx]

(ooxml, embedded labels, table is Sheet1);

//[ResultJoin]: the name here not is use

right join([ResultJoin])

Load *

Resident [MyCalendar];

[sunny_talwar]

I think all you need is to use Order's table in your resident load which is the result of the right join that you done:

[FinalTable]:

NoConcatenate

Load *

Resident [Orders];

In addition, you would need to use NoConcatenate if all you are trying to do is to create another field using *. If you add another field (such as a flag) you may not need it, but having it there doesn't harm you in anyway.

[#5]

Thanks for your suggestion to rename table after joining, I used it as well. Also, thanks for your brief explanation about the behaviour of join sentence. I didn't know that join will keep the name of the first table. Great!

[#6]

Hi, Rodrigo,

Thanks for the example and also for the explanation. Basically, the main problem was that I didn't know that the result of the join toke the first table as table result.

[#7]

In fact, I wanted Order's table to be the result of the join, and then replace the old one loaded in previous script with the new one resulting from the join, so I ended by creating a temp table loaded with Order's table, join it, and then the result of the join (the temp table) rename it to Orders (previously removing the old one that I don't need anymore).