topic Re: SCD in QlikView

SCD

tyagishaila — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community Members,

Below is the sample data,

Emp MonthOfJoning Dept

101 Feb D1

101 Apr D2

101 Sep D4

102 Feb D2

102 Oct D3

103 Mar D4

104 Apr D3

104 Sep D2

104 Dec D4

I need to find for each month how many employees were working in each department.

Thanks,

Re: SCD

tresB — Tue, 14 Jun 2016 05:50:16 GMT

Straight Table

Dim1:MonthOfJoining

Dim2:Dept

Exp: Count( Distinct Emp)

Re: SCD

Chanty4u — Tue, 14 Jun 2016 05:54:23 GMT

hi ,

PFA

Re: SCD

tyagishaila — Tue, 14 Jun 2016 06:58:22 GMT

Thanks Tresco for immediate response.

But it will not cover all Months (Jan -to-Dec)

Re: SCD

Chanty4u — Tue, 14 Jun 2016 07:02:56 GMT

it will. bcz Jan is not availble in ur requrmnt so it is not shwng.

i hve included jan in script see the result

Re: SCD

tyagishaila — Tue, 14 Jun 2016 07:03:32 GMT

Hi Chanty,

This qvw is not opened in my PC.

Can you share script of this.

Thanks

Re: SCD

tresB — Tue, 14 Jun 2016 07:03:40 GMT

Assuming you meant all months for all departments. Right, because you don't have data for all months for all departments. You have to generate the data at the back end. Check: How to populate a sparsely populated field for similar way.

Re: SCD

tyagishaila — Tue, 14 Jun 2016 07:31:04 GMT

I also need Emp must belong to same dept until its dept is not changed.

e.g. Emp 101 belongs to D1 in Feb and Mar Month

& in Apr, May Jun, Jul, Aug he is the employee of dept D2.

& in Sep, Oct, Nov and Dec belongs to D4 dept.

Hope my problem is more clear now.

Thanks

Re: SCD

pho3nix90 — Tue, 14 Jun 2016 09:10:46 GMT

Please find attached.

The model checks the start of the emp in the specific department and then calculates an end date depending if the employee belonged to another department previously. It then calculates how many employees was active within a department over months.

Also deselect "Suppress zero-values" on Presentation tab on the chart

Scripts:

Data:
LOAD
Emp,
Date(Date#(MonthOfJoning,'DD-MMM-YYYY')) as MonthOfJoning,
Dept
;
LOAD * INLINE [
    Emp, MonthOfJoning, Dept
    101, 01-Feb-2016, D1
    101, 01-Apr-2016, D2
    101, 01-Sep-2016, D4
    102, 01-Feb-2016, D2
    102, 01-Oct-2016, D3
    103, 01-Mar-2016, D4
    104, 01-Apr-2016, D3
    104, 01-Sep-2016, D2
    104, 01-Dec-2016, D4
];

Left Join
LOAD MonthOfJoning, if(Peek('Emp')=Emp,if(isnull(Peek('MonthOfJoning')),Date(AddMonths(MonthOfJoning, 999)),Peek('MonthOfJoning')),Date(AddMonths(MonthOfJoning, 999))) as End,Emp Resident Data Order By Emp, MonthOfJoning desc;

MinMax: LOAD Min(MonthOfJoning) as MinDate, Max(MonthOfJoning) as MaxDate Resident Data;
LET vMinDate = YearStart(Peek('MinDate'));
LET vMaxDate = Peek('MaxDate');
//DROP Table MinMax;
Calendar:
LOAD
Date('$(vMinDate)'+RecNo()) as Date,
Month('$(vMinDate)'+RecNo()) as Month,
Date#(Text(Date('$(vMinDate)'+RecNo(),'MMM YYYY')),'MMM YYYY') as YearMonth
AutoGenerate $(vMaxDate)-$(vMinDate);
DROP Table MinMax;

IntervalMatch(Date)
LOAD MonthOfJoning, End Resident Data;

Re: SCD

tyagishaila — Tue, 14 Jun 2016 10:08:00 GMT

Thanks Shaun