How to use JOINS or what is the efficient way of finding a way within the script through a 3rd table?

[jonnaamb]

Hello dear experts,

I have a situation here. I reduced the complexity in such a way that it would be easy to understand.

Problem statement: I want to achieve the Age of the Machine ( in days ) which is nothing but a difference between "Install Date" and "Order Date", which should finally be joined to Orders table as shown below.

Questions:

1. What is the efficient way of doing this? - Ideally I can achieve this using a straight table and an expression, but I don't want to because it would kill the performance.

2. Inner Join or Left Join?

Script:

Orders:

LOAD * INLINE [

    Link Key, Order Date

    A123_123, 20161211

    A124_124, 20141115

];

Machines:

LOAD * INLINE [

    Machine Key, Install Date

    A123, 20150605

    A124, 20140709

];

Link:

LOAD * INLINE [

    Link Key, Machine Key

    A123_123, A123

    A124_124, A124

];

Thank you so much in advance for all your inputs.

Many thanks,

Ambareesh Jonnavittula

[jonnaamb]

[effinty2112]

Hi Ambareesh,

You can try:

MappingTable:

Mapping

LOAD * INLINE [

    Link Key, Machine Key

    A123_123, A123

    A124_124, A124

];

Orders:

LOAD

*,

ApplyMap('MappingTable',[Link Key],'Missing') as [Machine Key];

LOAD * INLINE [

    Link Key, Order Date

    A123_123, 20161211

    A124_124, 20141115

];

Left Join(Orders)

LOAD * INLINE [

    Machine Key, Install Date

    A123, 20150605

    A124, 20140709

];

Drop Field [Link Key];

Now you can get this straight table:

Machine Key	Install Date	Order Date	Age of the Machine
A123	20150605	20161211	555
A124	20140709	20141115	129

Age of the Machine :

Interval(Date#([Order Date],'YYYYMMDD')-Date#([Install Date],'YYYYMMDD'),'D')

If you'd prefer to calculate the age in the script you can use the above expression:

[Machine Dates and Age]:

LOAD

*,

Interval(Date#([Order Date],'YYYYMMDD')-Date#([Install Date],'YYYYMMDD'),'D') as [Age of the Machine]

Resident Orders;

drop Table Orders;

Good luck

Andrew