Qlik Sense IntervalMatch (날짜간격에 조인)

비즈니스 인텔리전스의 일반적인 문제는 숫자를 범위에 연결하려는 경우입니다. 한 테이블에는 날짜가 있고 다른 테이블에는 간격("시작" 날짜 및 "종료" 날짜)이 있고 두 테이블을 연결하려고 할 수 있습니다. SQL에서는 비교 시 BETWEEN 절을 사용하여 두 항목을 조인할 수 있습니다.

 

해결방법

클릭센스에서는 IntervalMatch를 사용합니다.

IntervalMatch는 Load 또는 SELECT 문 앞에 붙을 수 있는 접두사입니다. Load/SELECT 문에는 간격을 정의하는 "From" 및 "To" 필드라는 두 개의 필드만 포함되어야 합니다. (본 예제에서는 약정일자 및 만기일자 입니다.)

 IntervalMatch는 로드된 간격과 이전에 로드된 숫자 필드(기준일자) 간의 모든 조합을 생성합니다.

 

일반적으로 먼저 개별 숫자(기준일자)가 포함된 테이블을 로드한 다음 간격이 포함된 테이블을 로드하고 마지막으로 두 개의 첫 번째 테이블을 연결하는 세 번째 테이블을 생성하는 간격 일치를 로드합니다.

(첫 번째와 두 번째의 테이블 로드는 순서를 변경하여도 됩니다. 그러나 IntervalMatch 접두사를 사용하는 로드문은 반드시 2개의 테이블이 적재된 후에 수행해야 합니다.)

 

차입금마스터 테이블의 구조

 

위 데이터를 연월별로 현황을 보고자 합니다.

 

IntervalMatch를 이용한 조인

 

결과 데이터 모델에는 세 개의 테이블 및 가상테이블이 생성됩니다.

기준년월은 년월을 1개씩 갖고 있는 테이블입니다. 기준일자는 기준년월의 시작일자입니다.

차입금 마스터는 약정일자와 만기일자를 갖고 있는 간격 테이블입니다.

Interval일자는 기준년월과 약정일자 및 만기일자 간격의 조합당 정확히 하나의 레코드를 포함하고 이전 두 테이블을 연결하는 테이블입니다.

이는 간격이 겹치는 경우 기준년월이 여러 간격에 속할 수 있음을 의미합니다. 물론 간격에는 여러 기준년월이 포함될 수 있습니다.

$Syn 1 Table은 2개 이상의 필드로 조인이 발생하면 가상의 테이블이 자동으로 생성됩니다. 이 테이블이 경고문을 발생하였지만 무시하여도 됩니다.

 

이 데이터 모델은 정규화 되었고 콤팩트한 점에서 최적의 모델입니다. 이러한 테이블에서 클릭센스는 정상적으로 작동하며 계산합니다. Count(차입금ID) 함수가 작동하고 올바르게 평가됩니다. 이는 Intervalmatch 테이블을 원래 테이블 중 하나에 조인할 필요가 없음을 의미합니다. 이를 다른 테이블에 조인하면 Qlik Sense가 집계를 잘못 계산할 수도 있습니다. 조인으로 인해 테이블의 레코드 수가 변경될 수 있기 때문입니다.

향 후 데이터 모델에는 Qlik Sense 합성 키로 나타나는 복합 키(약정일자 및 만기일자 필드)가 포함되어 있는 모델로 수정할 수 있습니다. 이 경우 합성 키가 반드시 있어야 합니다. 이는 정확할 뿐만 아니라 데이터 모델을 고려할 때 최적이기도 합니다. 제거할 필요는 없습니다.

단일 날짜에서 날짜 간격 생성

데이터에 유효 범위가 있지만 범위는 하나의 필드, 즉 단일 변경 날짜로만 표시되는 경우가 있습니다.

여러 통화에 대한 환율이 있는 아래쪽 표와 같을 수 있습니다. 각 환율 변경은 자체 행에 있습니다. 각각 새로운 전환율이 적용됩니다. 또한 테이블에는 첫 번째 변경이 이루어지기 전의 초기 전환율에 해당하는 빈 날짜가 있는 행이 포함될 수 있습니다.

 

위 테이블에 기준일자가 없는 레코드는 초기일자를 추가하여 시작일자로 지정하며 다른 행은 기준일자를 종료일자로 지정하고 이전 행의 기준일자를 시작일자로 지정합니다.

레코드를 삽입하고 이를 올바른 필드 값으로 채우는 대신 행 수를 유지하고 종료일자라는 새 열을 만들어 새 테이블이 간격 목록이 되도록 하겠습니다.

 

방법은 다음과 같습니다.

1.     작업할 시간 범위를 결정합니다. 범위의 시작은 데이터의 첫 번째 날짜 이전이어야 하고 범위의 끝은 마지막 날짜 이후여야 합니다.

2.     원본 데이터를 로드하되 빈 날짜를 이전 글머리 기호에 정의된 범위의 시작 부분으로 변경합니다. 변경 날짜는 "시작일자"로 로드되어야 합니다.

3.     먼저 통화를 기준으로 테이블을 정렬한 다음 "시작일자"를 기준으로 내림차순으로 정렬하여 최신 날짜가 맨 위에 오도록 합니다.

4.     소스 테이블 "종료일자"를 계산하는 데이터를 통해 두 번째 전달을 실행합니다. 현재 레코드에 이전 레코드와 다른 통화가 있는 경우 이는 새 통화의 첫 번째 레코드(그러나 마지막 간격)이므로 1 항목에 정의된 범위의 끝을 사용해야 합니다. 동일한 통화인 경우, 이전 레코드의 "시작일자"에서 약간의 시간을 뺀 다음 이 값을 현재 레코드의 "종료일자"로 사용해야 합니다.

 

 

이 작업이 완료되면 간격이 올바르게 나열되는 표가 생성됩니다. 그런 다음 이 테이블을 While 루프에서 사용하여 간격의 모든 날짜를 생성하거나(간격에 대한 참조 날짜 생성 참조) IntervalMatch와 함께 기존 날짜와 비교할 수 있습니다.

이 예에서는 이전 기록의 날짜에서 0.00000001을 뺍니다. 이는 대략 1밀리초에 해당합니다. 즉, "종료일자"는 자정 전 1밀리초의 값을 가지지만 날짜만 표시하도록 형식이 지정됩니다. 이렇게 하는 이유는 IntervalMatch를 작동하기 위해서 가공하는 것 입니다.

 

결과는 IntervalMatch 테이블이 생성되면서 기준일자와 Rates 테이블의 교차테이블이 생성됩니다. 이때 시작일자 및 종료일자 2 개의 필드로 조인이 되기 때문에 $syn1 Table 이란 가상의 테이블이 생성됩니다. 클릭센스에서는 2개의 필드로 조인이 될 경우 가상 테이블이 생성됩니다. 가상테이블은 교차테이블이란 개념으로 성능에는 문제가 없습니다. (가상테이블이 많이 생성되면 영향을 미칠 수 있습니다.)

가상테이블은 모델을 보기에는 혼란을 줄 수 있습니다.

IntervalMatch 후 생성된 가상테이블 제거

IntervalMatch 로 생성 후에는 가상의 테이블이 생성됩니다. 이를 해소하기 위해서는 원본 테이블과 생성된 테이블을 조인하여 해결할 수 있습니다.

해결하는 방법은 시작일자 및 종료일자를 결합한 필드를 생성하여 조인하면 됩니다.

 

가상 테이블 $syn1 Table 이 사라지면서 기준일자와 Rates 테이블이 직접 연결되었습니다.

이 대 Rates 테이블의 레코드 수는 변경됩니다.

 

단일 기준일자를 이용하여 연속된 일자별 레코드 생성

데이터를 로드할 때 필드가 드물게 채워지는 경우가 있습니다. 즉, 일부 일자의 값이 누락된 개별 일자의 값만 있습니다.

다음 예제에서 3개의 날짜에 전환율이 있습니다. 데이터에는 전환율이 변경된 날짜만 포함되며 변경 사이의 날짜는 포함되지 않습니다.

그러나 사용자는 "이 특정 날짜의 상태는 어땠나요?"라는 질문을 하고 싶어할 것입니다. 즉, 사용자는 참조 날짜를 클릭하여 이 날짜와 연관된 숫자를 볼 수 있기를 원하지만 해당 날짜가 소스 데이터에 없을 수도 있습니다.

이러한 상황에서는 변경 사항 사이의 누락된 날짜를 개별 기록으로 생성하고 이전 날짜의 "전환률" 값을 사용해야 합니다.

 

Qlik에서는 이를 수행하는 방법이 여러 가지가 있으며 모든 방법에는 임시 테이블을 사용하는 일부 스크립트 프로그래밍이 포함됩니다.  그 중 하나의 알고리즘입니다.

1.     전환률(이하 "전환률"이라고 함)이 포함된 소스 테이블을 로드합니다.

2.     '전환률' 표에서 가장 큰 날짜와 가장 작은 날짜를 찾습니다.

3.     가장 큰 날짜와 가장 작은 날짜(이하 "날짜"라고 함) 사이의 모든 날짜를 생성합니다.

4.     "날짜" 테이블(외부 조인)을 "전환률" 테이블에 조인합니다.

5.     결과 테이블을 날짜별로 정렬합니다.

6.     Peek() 함수를 사용하여 "전환률" 필드에 NULL이 있는 모든 레코드에 "전환률" 값을 아래쪽으로 전파합니다.

 

Qlik Sense IntervalMatch 날짜간격에조인.pdf

0.55MB