Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two SQL's the 1st one shows a table like this:
Employee_ID-----name-----salary
1-----------------John-------$1
2----------------David-------$1
The second table like this:
Employee_ID-----name-----salary
1------------------John------$2
3-----------------Merry------$2
Now I run the first SQL, & I use QUALIFY with the second one and I have all the information I need. Then I use an expression like this:
If(Emplyee_name = 1.Employee_name, 1.salary)
It supposes to show (John------$2)
But It shows null.
(1.employee_name or 1.salary are what I get after running the second SQL using Qualify.)
Is there a way to compare the two SQL's and having results?
Thank you in advance.
Hi meshal,
When you QUALIFY with the second statement, the second table becomes distinguished:
As you can see the second table gets "2" prefix for each field:
And in the front end, you should use specific values in order to get your functions to work:
So the solution is as follows:
put UNQUALIFY [Employee ID];
And then your data model will look like this:
And finally, in the front end you can do what ever you want to accomplish:
Hope this helps.
HI,
You can try like this joining two tables,
Temp:
LOAD
EmpID,
Name,
Salary AS Salary1
FROM Table1;
Left Join(Temp)
LOAD
EmpID,
Name,
Salary AS Salary2
FROM Table2;
Data:
LOAD
*,
If(Salary1 <> Salary2, 'Match', 'Not Match') AS SalaryMatchFlag
RESIDENT Temp;
DROP TABLE Temp;
Now in front end you can use below expression to Non match salary employees
Dimension: EmpID
Expression: Only{<SalaryMatchFlag={'Not Match'}>} Name)
Hope this helps you.
Regards,
Jagan.
Hi,
You can also try
HI,
You can try like this joining two tables,
Table1:
LOAD
EmpID,
Name,
Salary AS Salary1
FROM Table1;
QUALIFY *;
UNQUALIFY EmpID;
Table2:
LOAD
EmpID,
Name,
Salary AS Salary2
FROM Table2;
Now in front end you can use below expression to Non match salary employees
Dimension: EmpID
Expression: If(Salary1 <> Salary2, Name)
Hope this helps you.
Regards,
Jagan.
Hi Meshal,
Simple example based on the above
HTH - Andy
thank you for this it was helpful. Could i did it without joining the two tables? and could i show the two values for the same employee using qualify or must i join the tables?
thank you
May be something like this:
Script:
1:
LOAD * Inline [
EmpID, Name, Salary
1, John, 1
2, David, 1
];
QUALIFY *;
2:
LOAD * Inline [
EmpID, Name, Salary
1, John, 2
3, Merry,2
];
Data Model
Straight Chart
Dimension: 2.EmpID
2.Name
Expression: =Only({<[2.EmpID] = p(EmpID)>}[2.Salary])
Output:
HTH
Best,
Sunny