Hello Folks,
I'm working with some script, and for some reason, when I perform a load with a Sum after a Distinct, it comes back with the wrong number. Below is the INCORRECT version of the script which gives me 8 usage days:
TractorUsageDatesBroken:
LOAD tractor_id,
tractor_id & '\' & MONTH(lgh_date) & '\' & YEAR(lgh_date) AS 'tractor_usage_key',
SUM(Iterate) AS 'tractor_usage_days',
YEAR(lgh_date) AS 'lgh_header_year',
MONTH(lgh_date) AS 'lgh_header_month'
GROUP BY tractor_id, YEAR(lgh_date), MONTH(lgh_date)
;
LOAD DISTINCT tractor_id, lgh_date, Iterate
;
LOAD tractor_id, Date(start_date + IterNo() - 1) AS 'lgh_date', 1 AS 'Iterate'
WHILE start_date + (IterNo() - 1) <= end_date
;
LOAD tractor_id, Date(start_date_text) AS 'start_date', Date(end_date_text) AS 'end_date'
;
LOAD * Inline
[
'tractor_id', 'start_date_text', 'end_date_text',
'17003', '03/01/2018', '03/02/2018',
'17003', '03/02/2018', '03/03/2018',
'17003', '03/05/2018', '03/06/2018',
'17003', '03/06/2018', '03/07/2018'
]
;
Below is the CORRECT version of the script which gives me a correct 6 days of usage.
Qualify *
;
TractorUsageDatesCorrect:
LOAD DISTINCT tractor_id, lgh_date, Iterate
;
LOAD tractor_id, Date(start_date + IterNo() - 1) AS 'lgh_date', 1 AS 'Iterate'
WHILE start_date + (IterNo() - 1) <= end_date
;
LOAD tractor_id, Date(start_date_text) AS 'start_date', Date(end_date_text) AS 'end_date'
;
LOAD * Inline
[
'tractor_id', 'start_date_text', 'end_date_text',
'17003', '03/01/2018', '03/02/2018',
'17003', '03/02/2018', '03/03/2018',
'17003', '03/05/2018', '03/06/2018',
'17003', '03/06/2018', '03/07/2018'
]
;
TractorUsageDatesCorrect2:
LOAD TractorUsageDatesCorrect.tractor_id,
TractorUsageDatesCorrect.tractor_id
& '\' & MONTH(TractorUsageDatesCorrect.lgh_date)
& '\' & YEAR(TractorUsageDatesCorrect.lgh_date) AS 'tractor_usage_key',
SUM(TractorUsageDatesCorrect.Iterate) AS 'tractor_usage_days',
YEAR(TractorUsageDatesCorrect.lgh_date) AS 'lgh_header_year',
MONTH(TractorUsageDatesCorrect.lgh_date) AS 'lgh_header_month'
Resident TractorUsageDatesCorrect
GROUP BY TractorUsageDatesCorrect.tractor_id,
YEAR(TractorUsageDatesCorrect.lgh_date),
MONTH(TractorUsageDatesCorrect.lgh_date)
;
DROP Table TractorUsageDatesCorrect
;
I know DISTINCT can be strange in the Qlik world, but I'm not sure why it's giving me the wrong number.
Any help is greatly appreciated.
