topic Personel First two months data not shown in App Development

Personel First two months data not shown

krmvacar — Thu, 23 Nov 2023 17:49:25 GMT

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

Re: Personel First two months data not shown

BrunPierre — Thu, 23 Nov 2023 18:46:51 GMT

@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;

Re: Personel First two months data not shown

anat — Fri, 24 Nov 2023 03:39:23 GMT

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

Re: Personel First two months data not shown

sidhiq91 — Fri, 24 Nov 2023 07:27:15 GMT

@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.