topic Re: Slowly Changing Dimension in Qlik Automate

Slowly Changing Dimension

Tanisha_36 — Sat, 10 Aug 2024 12:59:12 GMT

there is one table as employee that has fields : emp id, terr id,joining date
another table as sales history which has : emp id, terr id, start date, end date

employee
Load*Inline [
empid, emp_name, terr_id,start_date
1001, Alice Johnson, T01 ,2023-01-15
1002, Bob Smith,T02,2023-02-15
1003, Carol White,T03,2023-03-15
1004, David Brown,T04,2024-02-01
1005, Eva Green, T05,2024-10-01
]
;

sales_hist:
load *inline
[empid,terr_id,start_date,end_date
1001,T01,2023-01-15,2023-05-15
1001,T02,2023-05-16,2024-06-31
1004,T04,2024-02-01,2024-08-01
1004,T03,2024-08-02,2024-12-31
];

now i want to know for that particular sales which emp is assign in that particular time period.

how to manage employee and sales history table

Thanks in advance

Re: Slowly Changing Dimension

anat — Sat, 10 Aug 2024 15:31:34 GMT

give a try with Extended IntervalMatch

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/IntervalMatch_(Extended_Syntax).htm#

Re: Slowly Changing Dimension

Tanisha_36 — Sun, 11 Aug 2024 09:42:19 GMT

Thank You
but cold you please explain how can I do so I am not able to do so

Re: Slowly Changing Dimension

anat — Sun, 11 Aug 2024 15:54:42 GMT

sales_hist:
load empid&terr_id as key ,start_date,end_date;
load * inline
[empid,terr_id,start_date,end_date
1001,T01,2023-01-15,2023-05-15
1001,T02,2023-05-16,2024-06-31
1004,T04,2024-02-01,2024-08-01
1004,T03,2024-08-02,2024-12-31
];

employee:
load empid&terr_id as key,empid, emp_name, terr_id,start_date as start_date1;
Load * Inline [
empid, emp_name, terr_id,start_date
1001, Alice Johnson, T01 ,2023-01-15
1002, Bob Smith,T02,2023-02-15
1003, Carol White,T03,2023-03-15
1004, David Brown,T04,2024-02-01
1005, Eva Green, T05,2024-10-01
]
;

//exit SCRIPT;
Inner Join IntervalMatch (start_date1,key) LOAD start_date, end_date, key resident sales_hist;