Too Many Rows

tmumaw — Thu, 27 Apr 2017 12:32:35 GMT

Can someone please tell me why I would generate 280 million rows when I am only taking in 12 million based on my script? I'm lost.

Thanks

Temp:
LOAD
[End Date],
[Start Date] as [Max_Start Date],
Year((Date(Floor([Start Date])))) as StartYear,
num(Date(Floor([Start Date]))) as NumStartDate,
[Employee Number],
[Reason f.Action_MASSG],
[Action Type_MASSN] as [Action Type],
Employment_STAT2,
[Reason f.Action_MASSG] & [Action Type_MASSN] as [Term Event]
FROM
(qvd)
Where [End Date] = '9999-12-31' and
(Employment_STAT2 = '0' or
Employment_STAT2 = '1' or
Employment_STAT2 = '2')
;

Temp1:
LOAD *,
if(Match([Action Type],'24','25','26','27','77','78','79','84'),1,0) as InActive
Resident Temp
Where Year([Max_Start Date]) >= '$(vPrevYear)'
;

DROP Table Temp;

TempDate:
LOAD FISPD,
FISYR,
[Billing Date] as [Max_Start Date],
WKNO
FROM (qvd)
where FISYR >= '$(vPrevYear)' and FISYR < '$(vYear)';

Left join (TempDate)
LOAD * INLINE [FISPD, QTR
001, 1,
002, 1,
003, 1,
004, 2,
005, 2,
006, 2,
007, 3,
008, 3,
009, 3,
010, 4,
011, 4,
012, 4,
];

Left join (Temp1)
Load *
Resident TempDate;

Drop table TempDate;

Left join (Temp1)
LOAD
[Employee Number],
[Profit Center],
Job_STELL
FROM

(qvd)
where [End Date] = '9999-12-31'
;

Employee_Detail:
Load *,
AutoNumber([Profit Center] & FISYR & QTR & FISPD & WKNO) as %LinkKey
Resident Temp1;

Drop Table Temp1;

exit script;

Re: Too Many Rows

sunny_talwar — Thu, 27 Apr 2017 12:36:21 GMT

You have bunch of joins in your script... if any of these joins are 1 to many or many to many, the data will increase exponentially

Re: Too Many Rows

tmumaw — Thu, 27 Apr 2017 12:39:17 GMT

But when I do a table dump I only should 12 million rows going into the generation of employee_detail.

topic Re: Too Many Rows in QlikView

Too Many Rows

Re: Too Many Rows

Re: Too Many Rows