Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am preparing a human resources report and I have been asked not to show the data of the staff for the first 60 days after starting work.
Table_A:
Emp_number | Start_date |
19383 | 20.09.2023 |
19381 | 01.08.2023 |
Table_B:
Emp_number | Work_date | Order_ıd |
19383 | 23.11.2023 | 73402 |
19383 | 15.11.2023 | 234202 |
19383 | 18.11.2023 | 454555 |
19381 | 06.09.2023 | 89987 |
19381 | 09.09.2023 | 34433 |
19381 | 06.11.2023 | 435345 |
I want to result :
Emp_number | Work_date | Order_ıd |
19383 | 23.11.2023 | 73402 |
19381 | 06.11.2023 | 435345 |
First 60 days ı want to not show result.
Please Help
@krmvacar as below.
Table_A:
LOAD Emp_number,
Date(Date#(Start_date,'DD.MM.YYYY'),'DD.MM.YYYY') as Start_date
FROM
[https://community.qlik.com/t5/App-Development/Personel-First-two-months-data-not-shown/td-p/2140906]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);
Join
Table_B:
LOAD Emp_number,
Date(Date#(Work_date,'DD.MM.YYYY'),'DD.MM.YYYY') as Work_date,
Order_ıd
FROM
[https://community.qlik.com/t5/App-Development/Personel-First-two-months-data-not-shown/td-p/2140906]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);
NoConcatenate
FilteredData:
LOAD Emp_number,
Work_date,
Order_ıd
Resident Table_A
WHERE Work_date >= Start_date + 60; // Exclude data for the first 60 days
DROP Table Table_A;
@krmvacar Please use the below code:
NoConcatenate
TableA:
Load Emp_number,
Date(Date#(Start_date,'DD.MM.YYYY'),'MM/DD/YYYY') as Start_date
Inline [
Emp_number, Start_date
19383, 20.09.2023
19381, 01.08.2023
];
Inner join (TableA)
TableB:
Load Emp_number,
Order_ıd,
Date(Date#(Work_date,'DD.MM.YYYY'),'MM/DD/YYYY') as Work_date
Inline [
Emp_number, Work_date, Order_ıd
19383, 23.11.2023, 73402
19383, 15.11.2023, 234202
19383, 18.11.2023, 454555
19381, 06.09.2023, 89987
19381, 09.09.2023, 34433
19381, 06.11.2023, 435345
];
NoConcatenate
Final:
Load Emp_number,[Order_ıd],
Work_date
where Numberofdays>60;
Load *,
Interval(Work_date-Start_date,'DD') as Numberofdays
Resident TableA;
Drop table TableA;
Exit Script;
If this resolves your issue, please like and accept it as a solution.
@krmvacar as below.
Table_A:
LOAD Emp_number,
Date(Date#(Start_date,'DD.MM.YYYY'),'DD.MM.YYYY') as Start_date
FROM
[https://community.qlik.com/t5/App-Development/Personel-First-two-months-data-not-shown/td-p/2140906]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);
Join
Table_B:
LOAD Emp_number,
Date(Date#(Work_date,'DD.MM.YYYY'),'DD.MM.YYYY') as Work_date,
Order_ıd
FROM
[https://community.qlik.com/t5/App-Development/Personel-First-two-months-data-not-shown/td-p/2140906]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);
NoConcatenate
FilteredData:
LOAD Emp_number,
Work_date,
Order_ıd
Resident Table_A
WHERE Work_date >= Start_date + 60; // Exclude data for the first 60 days
DROP Table Table_A;
Table1:
mapping load Emp_number ,Start_date from Table1;
Table2:
load Emp_number ,Work_date,Order_ıd,
applymap('Table1',Emp_number) as Start_date
from Table2;
noconcatenate
Table3:
load *,Start_date-Work_date as days resident Table2;
drop table Table2;
in front end you can use days field to restrict N number of days data
@krmvacar Please use the below code:
NoConcatenate
TableA:
Load Emp_number,
Date(Date#(Start_date,'DD.MM.YYYY'),'MM/DD/YYYY') as Start_date
Inline [
Emp_number, Start_date
19383, 20.09.2023
19381, 01.08.2023
];
Inner join (TableA)
TableB:
Load Emp_number,
Order_ıd,
Date(Date#(Work_date,'DD.MM.YYYY'),'MM/DD/YYYY') as Work_date
Inline [
Emp_number, Work_date, Order_ıd
19383, 23.11.2023, 73402
19383, 15.11.2023, 234202
19383, 18.11.2023, 454555
19381, 06.09.2023, 89987
19381, 09.09.2023, 34433
19381, 06.11.2023, 435345
];
NoConcatenate
Final:
Load Emp_number,[Order_ıd],
Work_date
where Numberofdays>60;
Load *,
Interval(Work_date-Start_date,'DD') as Numberofdays
Resident TableA;
Drop table TableA;
Exit Script;
If this resolves your issue, please like and accept it as a solution.