topic Re: Set expression 1 in QlikView

Set expression 1

prasad333 — Sun, 19 Nov 2017 04:11:06 GMT

Hi everyone,

I just came across a situation. So here it is -

Suppose you have two tables

Tab1: Tab2:

EMP SALARY EMP SALARY

A 10000 D 40000

B 20000 E 50000

C 30000 F 60000

D 40000 G 70000

E 50000

1. I have to show the salaries of those employees who are

a) present in tab1 but not in tab2

b) present in both the tabs

c) 3 highest paid employees from both the tabs

d) employees who have the same salary status

- write the solutions for both front end and back end.

Regards,

KHARANSU RATH

Re: Set expression 1

arvind_patil — Sun, 19 Nov 2017 17:12:03 GMT

HI Karansu,

Please do as follows:

a) present in tab1 but not in tab2

Where exist

b) present in both the tabs

Inner join

c) 3 highest paid employees from both the tabs

Concatenate both tables and Dimesion limit top 3:

d) employees who have the same salary status:

Employee& salary as Key

inner join both the tables.

Thanks,

Arvind Patil

Re: Set expression 1

prasad333 — Sun, 19 Nov 2017 19:18:26 GMT

Hey Arvinnd,

first of all thank you for replying but I would like to stretch this discussion a bit. For question a & b, what set expressions should I write to get the required results?

Re: Set expression 1

Anil_Babu_Samineni — Mon, 20 Nov 2017 08:25:33 GMT

Perhaps this?

Qualify *;

Tab1:

Load EMP, SALARY From T1;

Concatenate (Tab1)

QUALIFY *;

Tab2:

Load EMP, SALARY From T2;

1) Sum(Tab1.SALARY)

2) RangeSum(Tab1.SALARY, Tab2.SALARY)

3) Sum({<Tab1.EMP = {"=Rank(-Sum(Tab1.SALARY)) <= 3"}, Tab2.EMP = {"=Rank(-Sum(Tab2.SALARY)) <= 3"}>} SALARY)

4) RangeSum(If(Tab1.SALARY = Tab2.SALARY), Tab1.EMP & Tab2.EMP)