I would like to only load the data from an access database if it meets the following criteria after all this code happens. Basically the code below is consolidating multiple rows into one so that I can track by hours.
Prod = 0 and Comm= Blank and Rsn = Blank
How can I accomplish this? Attached is an excel sheet of sample data that I would not like to load in pink.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
ODBC CONNECT32 TO [MS Access Database;DBQ=V:\HourlyBoards.mdb];
tabInput:
CrossTable (FieldName, FieldValue, 5)
LOAD RecNo() as %ID, *;
SQL
SELECT *
FROM `tbl_BoardHistoryNew`
WHERE Date > 41640;
Left Join (tabInput)
LOAD Distinct
FieldName,
SubField(FieldName, '_', 2) as FieldNameNew,
SubField(FieldName, '_', 1) as Hour
Resident tabInput;
Left Join (tabInput)
LOAD Distinct
Date,
Date(Date) as date
Resident tabInput;
tabOutput:
LOAD Distinct
%ID,
Line,
Shift,
date,
Manning as [Total Manning],
year(date) as Year,
year(date) & '-'& Month(Date) as YearMonth,
Month(date) as Month,
Dual(LEFT(Month(date), 1), Month(date)) as Month_abbreviation/*
