Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER

Beginners Task Series - Task3(Interval Match)

Specialist III
Specialist 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
Partner
Partner

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.

0 Likes
Contributor III
Contributor III

Very Helpful 🙂

0 Likes
Creator
Creator

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;

Specialist III
Specialist III

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

0 Likes
Creator III
Creator III

this is very help full beginners,

thanks kiran sir

0 Likes
Explorer
Explorer

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;

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