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

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

jonnaamb — Wed, 25 Nov 2020 16:16:04 GMT

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

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

jonnaamb — Wed, 26 Jul 2017 18:44:24 GMT

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

effinty2112 — Wed, 26 Jul 2017 20:40:40 GMT

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