Dữ liệu Date/ Time trong Qlik (P1)

Căn bản về quá trình nhận diện ngày tháng trong Qlik khá đơn giản: khi Qlik engine phát hiện một field dữ liệu “trông giống” ngày tháng từ dữ liệu nguồn, nó sẽ thử áp dụng các chuỗi định dạng (format string) từ một danh sách sẵn có lên field. Nếu toàn bộ giá trị trong field phù hợp với một format string, field này sẽ được hiểu là 1 data field. Đơn giản là vậy, nhưng cũng có nhiều hiểu lầm phổ biến về cách thức nhận dạng ngày tháng trong Qlik.

Trong phần lớn trường hợp, Qlik engine có khả năng tự động nhận diện kiểu dữ liệu ngày tháng (date) hoặc thời điểm (timestamp) nếu bạn sử dụng data load wizard (File Wizard trong QlikView hoặc Data Manager trong Qlik Sense). Tuy nhiên, vẫn có nhiều trường hợp bạn cần phải can thiệp vào quá trình nhận diện này khi các date fields trong dữ liệu của bạn được lưu với một định dạng không có trong danh sách định dạng chuẩn của Qlik. Thường gặp là date không dùng ký tự phân chia ngày tháng (ví dụ 20170105) hoặc các nguồn dữ liệu địa phương với định dạng không chuẩn, ví dụ “27 tháng 8, 2017”.

Kiểu dữ liệu Date/ Time trong Qlik

Ngạc nhiên đầu tiên – không có kiểu Date/ Time trong Qlik! Thật ra là Qlik không có khái niệm “kiểu dữ liệu”, hoặc ít ra cũng không giống với cách hiểu về field type trong các hệ thống database phổ biến. Thay vào đó, Qlik lưu mỗi giá trị dữ liệu thành 2 phần: giá trị hiển thị dạng string, và giá trị tính toán dạng số. Giá trị hiển thị luôn có ở tất cả field dữ liệu, trong khi giá trị tính toán chỉ có ở các field có thể tính toán được.

Date/ Time là thể hiện rõ nhất của cách lưu trữ song song (dual values) này. Ví dụ, 12 giờ trưa ngày 17/12/2017 có giá trị hiển thị là 17.12.2017 12:00:00 (có thể thay đổi tùy vào format string), và giá trị tính toán là 43086.5 . Giá trị tính toán 43086.5 sẽ chỉ là một con số chung chung và không có ý nghĩa nào cụ thể nếu không có giá trị hiển thị đi kèm.

Tại sao lại là 43086.5? Phần nguyên (43086) là số ngày đã trôi qua tính từ 30/12/1899. Phần thập phân (0.5) là thời gian tính từ đầu ngày , tăng dần từ 0 (0:00) đến 1 (24:00). Qlik sử dụng chung date system với Microsoft và Google, trong đó lấy ngày 30/12/1899 làm ngày đầu tiên (base date). Numeric value của các ngày khác được tính dựa trên khoảng cách thời gian với base date.

Lợi điểm của cách lưu trữ song song này là dữ liệu có thể được tính toán, so sánh và sắp thứ tự (sort) trong khi vẫn được hiển thị theo định dạng thân thiện với người dùng. Trong ví dụ bên dưới, nếu cột TransMonth chỉ được lưu dạng text thông thường, khi sort bảng theo tháng tăng dần sẽ cho ra thứ tự sai là “Dec – Feb – Jan – Jun”. Một ví dụ khác: tính 20 ngày trước ngày 17.12.2017 ? Qlik dễ dàng tính toán giá trị này bằng cách lấy 43086 – 20.

Theo cách hiểu trên về Dual Values, giải quyết các vấn đề về date/time trong Qlik thực chất là đảm bảo Qlik nhận diện đúng giá trị hiển thị và giá trị tính toán của các date field. Dưới đây là một số tips để đơn giản hóa việc xử lý ngày tháng trong Qlik.

1. Tận dụng system variables:

Phần đầu tiên của reload script trong Qlik là các câu lệnh set giá trị cho các system variable, trong đó có nhiều variable liên quan đến định dạng ngày tháng. Một số variable mà bạn cần lưu ý tận dụng:

  • DateFormat và TimestampFormat: format string mặc định cho dữ liệu kiểu ngày tháng. Đặc biệt hữu dụng khi tất cả hoặc phần lớn các date/time field trong data source của bạn đã được format theo một định dạng nhất định. Format string trong Qlik có dạng ‘MM/DD/YYYY hh:mm:ss[.fff] TT’ (ngày/ tháng/ năm giờ:phút:giây.phần trăm giây AM/PM). Bạn có thể thay đổi thứ tự và ký tự phân chia ngày tháng. Ví dụ date field của bạn có dạng 2017-12-31, format string sẽ là ‘YYYY-MM-DD’. Lưu ý đặc biệt đối với format string của tháng:
    • Dùng ký tự M in hoa, phân biệt với m thường (minutes) dành cho phút
    • ‘M’ hoặc ‘MM’ => số tháng từ 0 tới 12
    • ‘MMM’ => Tên rút gọn của tháng (“Jan”)
    • ‘MMMM’ => Tên đầy đủ của tháng (“January”)
  • MonthNames, LongMonthNames, DayNames, LongDayNames: Tên gọi rút gọn/ đầy đủ của các tháng/ ngày trong tuần. Sửa giá trị các variable này nếu bạn muốn ứng dụng của bạn hiển thị “Tháng 1” thay cho “Jan”, “Thứ ba” thay cho “Tue”.

2. Sử dụng các hàm nhận diện/ định dạng ngày tháng

Nếu bạn load dữ liệu từ nhiều nguồn với nhiều định dạng ngày tháng khác nhau, bạn cần dùng các nhóm hàm sau trong reload script:

  • Nhóm hàm nhận diện (interpretation) ngày tháng: Bao gồm Date#()và Timestamp#(). Lưu ý dấu # phía sau tên hàm. Các hàm này có thể hiểu là string-to-number, chuyển date string từ data source sang giá trị tính toán trong QlikView
  • Nhóm hàm định dạng (formatting) ngày tháng: Bao gồm Date() và Timestamp() – không có # phía sau tên hàm. Các hàm này có thể hiểu là number-to-string, chuyển đổi giá trị tính toán của date value sang giá trị hiển thị.

Các hàm này thường được sử dụng lồng (nested) vào nhau. Ví dụ, bạn thường thấy cách dùng phổ biến sau: Date( Date#( TransactionDate, ‘MMM/DD/YY’), ‘YYYY-MM-DD’) as TransDate => Nhận diện field TransactionDate với định dạng ‘MMM/DD/YY’ từ dữ liệu nguồn, chuyển đổi thành field TransDate trong Qlik có cặp giá trị tính toán + giá trị hiển thị theo định dạng ‘YYYY-MM-DD’.

Ngoài ra vẫn còn một số tips khác khi làm việc với dữ liệu Date/Time trong Qlik, mình sẽ tiếp tục chia sẻ với các bạn trong phần 2

(Nguồn : https://vietqlikies.wordpress.com)