topic Re: Comparing 2 SQL's in QlikView

Comparing 2 SQL's

Wed, 15 Jul 2015 21:49:24 GMT

‌

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.

Re: Comparing 2 SQL's

sinanozdemir — Thu, 16 Jul 2015 01:04:16 GMT

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.

Re: Comparing 2 SQL's

jagan — Thu, 16 Jul 2015 02:30:57 GMT

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.

Re: Comparing 2 SQL's

jagan — Thu, 16 Jul 2015 02:33:54 GMT

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.

Re: Comparing 2 SQL's

awhitfield — Thu, 16 Jul 2015 11:05:43 GMT

Hi Meshal,

Simple example based on the above

HTH - Andy

Re: Comparing 2 SQL's

Sat, 18 Jul 2015 17:29:55 GMT

‌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

Re: Comparing 2 SQL's

sunny_talwar — Sat, 18 Jul 2015 18:04:46 GMT

May be something like this:

Script:

LOAD * Inline [

EmpID, Name, Salary

1, John, 1

2, David, 1

];

QUALIFY *;

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