Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Beginners Task Series - Task3(Interval Match)

kkkumar82
Valued Contributor III

Beginners Task Series - Task3(Interval Match)

All,

Beginners in Qlikview I am back with another scenario i.e. Interval Match.

Scenario:

I have two tables Employees, Job grades. The task here is to find out the Grade level of the employees. The same can be achieved in SQL as below using joins

Select FIRST_NAME,SALARY,GRADE_LEVEL

from employees,job_grades

Where employees.SALARY >= job_grades.LOWEST_SAL and employees.SALARY <= job_grades.HIGHEST_SAL

Coming to Qlik view we do a interval match for this, the following code is what I used.

Directory;

LOAD EMPLOYEE_ID,

    FIRST_NAME,

    LAST_NAME,

    EMAIL,

    PHONE_NUMBER,

    HIRE_DATE,

    JOB_ID,

    SALARY,

    COMMISSION_PCT,

    MANAGER_ID,

    DEPARTMENT_ID

FROM

employees.xlsx

(ooxml, embedded labels, table is employees);

Job_Grades:

LOAD GRADE_LEVEL,

    LOWEST_SAL,

    HIGHEST_SAL

FROM

[job grades.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

IntervalMatch(SALARY)

LOAD LOWEST_SAL,HIGHEST_SAL

Resident Job_Grades;

Note: One noticeable point here is, I couldn't get rid of the synthetic keys. Is there a way that can be done.

Hope this helps.

Thanks

Kiran Kumar

Labels (1)
Attachments
Comments
consenit
Contributor II

Hi there.

The synthetic key generated by IntervalMatch() is completely harmless and quite efficient, there's usually no need to remove it. However, if you want to do it anyway check out this document:

IntervalMatch and Slowly Changing Dimensions

Kind regards,

Ernesto.

chaitrang
New Contributor II

Very Helpful :-)

sreeni_qvd
New Contributor III

Employees:

LOAD EMPLOYEE_ID,

     FIRST_NAME,

     LAST_NAME,

     EMAIL,

     PHONE_NUMBER,

     HIRE_DATE,

     JOB_ID,

     SALARY,

     COMMISSION_PCT,

     MANAGER_ID,

     DEPARTMENT_ID

FROM

(ooxml, embedded labels, table is employees);

Job_Grades:

LOAD GRADE_LEVEL,

     LOWEST_SAL,

     HIGHEST_SAL

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Left Join

IntervalMatch(SALARY)

LOAD LOWEST_SAL,HIGHEST_SAL

Resident Job_Grades;

kkkumar82
Valued Contributor III

Awesome .. its good to see joins in this way useful.

satheshreddy
Contributor III

this is very help full beginners,

thanks kiran sir

ashishc792
Visitor

Employees:

LOAD

    EMPLOYEE_ID,

    FIRST_NAME,

    LAST_NAME,

    EMAIL,

    PHONE_NUMBER,

    HIRE_DATE,

    JOB_ID,

    SALARY,

    COMMISSION_PCT,

    MANAGER_ID,

    DEPARTMENT_ID

FROM [lib://data/employees.xlsx]

(ooxml, embedded labels, table is employees);

Job_grades:

LOAD

    GRADE_LEVEL,

    LOWEST_SAL,

    HIGHEST_SAL

FROM [lib://data/job grades.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

inner join (Employees)

IntervalMatch(SALARY)

Load

  LOWEST_SAL,

    HIGHEST_SAL

Resident Job_grades;

Left Join(Employees)

Load *

resident Job_grades;

drop table Job_grades;

Version history
Revision #:
1 of 1
Last update:
‎04-02-2016 03:09 PM
Updated by: