Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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 ...
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.
A:
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
];
B:
load * inline
[SalaryGrade, MinSG, MaxSG
Grade 01, 30000, 40000
Grade 02, 40000, 45000
Grade 03, 45000, 55000
];
C:
IntervalMatch(GrossSalary) Load MinSG, MaxSG resident B;
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
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
I noticed that the first solution is an inner join and not a left join ....
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
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.
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
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