topic Re: Left Join with script in QlikView

Left Outer Join with script

Anonymous — Tue, 10 Dec 2013 13:41:00 GMT

Hi, can anyone help me!

I have to transate the simple sql statement:

Select A.*, B.SalaryGrade

from A Left outer join B

on a.Salary between B.MinSG and b:MaxSG

I have try with "IntervalMatch" but i'm not able to have the final result.

Simplifying I have table A:

EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary

0000001, xxxxx, yyyyyy, 2013, 1, 45000

0000001, xxxxx, yyyyyy, 2013, 2, 45000

0000001, xxxxx, yyyyyy, 2013, 3, 50000

And the Table B:

SalaryGrade, MinSG, MaxSG

Grade 01, 30000, 40000

Grade 02, 40000, 45000

Grade 03, 45000, 55000

I want have the table C like this:

EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary, SalaryGrade

0000001, xxxxx, yyyyyy, 2013, 1, 45000, Grade 02

0000001, xxxxx, yyyyyy, 2013, 2, 45000, Grade 02

0000001, xxxxx, yyyyyy, 2013, 3, 50000, Grade 03

Thank for help

Re: Left Join with script

sasikanth — Tue, 10 Dec 2013 13:56:06 GMT

Hello Dario

You better to use Intevelmatch() function here

Table B:

SalaryGrade, MinSG, MaxSG

Grade 01, 30000, 40000

Grade 02, 40000, 45000

Grade 03, 45000, 55000

Table A:

EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary

0000001, xxxxx, yyyyyy, 2013, 1, 45000

0000001, xxxxx, yyyyyy, 2013, 2, 45000

0000001, xxxxx, yyyyyy, 2013, 3, 50000

Table C:

IntervalMatch(GrossSalary) Load MinSG,MaxSg resident Table B;

Hope you can get with this ...

Re: Left Join with script

Anonymous — Tue, 10 Dec 2013 14:08:25 GMT

Hi Sasi,

thank for you reply but i don't want have two table with a link. I want have only one table.

I have just try this solution

and don't work fine GrossSalary 45000 is Grade02 and Grade03.

load * inline

[EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary

0000001, xxxxx, yyyyyy, 2013, 1, 45000

0000001, xxxxx, yyyyyy, 2013, 2, 45000

0000001, xxxxx, yyyyyy, 2013, 3, 50000

];

load * inline

[SalaryGrade, MinSG, MaxSG

Grade 01, 30000, 40000

Grade 02, 40000, 45000

Grade 03, 45000, 55000

];

IntervalMatch(GrossSalary) Load MinSG, MaxSG resident B;

Re: Left Join with script

Tue, 10 Dec 2013 14:38:41 GMT

Dario,

In fact you can create the IntervalMatch table and use it afterwards.

By using this function, you should not have overlapping grades, for example 45000 may belong to several grades.

grade 1 can finish at 45000 but grade2 must begin at 45001 or 45000.01

//SOLUTION 1 : problem, your tables must have very few rows because the first JOIN will create many rows (nb of rows of table 1 x nb of rows of table 2). But it is easy to understand. First you do a JOIN (with a lot of rows), after that you create the real table with a WHERE statement

Join (Temp_TableA)
LOAD * Resident Temp_TableB;

TableA:
LOAD EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary, SalaryGrade
Resident Temp_TableA
Where GrossSalary >= MinSG AND GrossSalary < MaxSG;

Drop tables Temp_TableA, Temp_TableB;

// SOLUTION 2: with the IntervalMatch that populates a temp_tableC, that is used for the join with TableA

But you still don't have what you wanted first. I can do a secondary JOIN to get the SalaryGrade (I use the XX field I have create to do the JOIN)

Temp_TableC:
INTERVALMATCH(GrossSalary)
LOAD distinct MinSG, MaxSG
Resident Temp_TableB;

LEFT Join (Temp_TableA)
LOAD GrossSalary, MinSG & '-'& MaxSG as XX
Resident Temp_TableC;

LEFT Join (Temp_TableA)
LOAD SalaryGrade, MinSG & '-'& MaxSG as XX
Resident Temp_TableB;

Drop tables Temp_TableB, Temp_TableC;
Drop field XX;

Fabrice

Re: Left Join with script

Anonymous — Tue, 10 Dec 2013 14:54:29 GMT

Hi Aunez,

thank for your help.

I prefer the first solution (it's most heavy for the machine) but i can have a contiguos range.

Regards

Re: Left Join with script

Anonymous — Tue, 10 Dec 2013 15:10:01 GMT

I noticed that the first solution is an inner join and not a left join ....

Re: Left Join with script

Wed, 11 Dec 2013 07:58:12 GMT

Dario,

In fact, there is no common fields (so inner JOIN ???, yes, that is the default keyword).

That is why you will get a large table: nb of rows of A X nb of rows of Table B.

If you get your result, please check the answer as correct for other people looking for the same solution.

Fabrice

Re: Left Outer Join with script

Anonymous — Wed, 11 Dec 2013 09:00:18 GMT

Hello Aunez,

In the title I had forgotten the word Outer, so I need to do a left outer join.

The first solution do a perfect LEFT JOIN, but if there are records in table A that have have values outside the range of Table B, these records are omitted in the resulting table C.

In the second solution is the problem of contiguity, I can't know the number of decimal that are upload by excel sheet.

To this the answer does not satisfy the requirements.

Re: Left Outer Join with script

Wed, 11 Dec 2013 09:43:02 GMT

Dario,

You should have all possible intervals, if not some rows will be lost. You could create an extra one: 50000 to 100 millions to be sure to catch everyone

These intervals must not overlap because you will get a salary belonging to several intervals. Not very good, I think.

You can use a trick I have seen in HIC's post: http://community.qlik.com/docs/DOC-4310

Create a variable with a very small number:

vEpsilon=pow(2, -27)

If you have a number belonging to 2 intervals, like 45000, you can let this boundary as it is for one, and do this boundary - (minus) vEpsilon for the other.

Fabrice

Re: Left Outer Join with script

Anonymous — Wed, 11 Dec 2013 13:27:56 GMT

Hi, your solution is working.

I have modified the first solution and now it work like a left outer join, but it's not performant and not elegant.

It's incredible that it's impossible make a "Left outer join" in the script using to load data.

Thanks