Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to set a conditional on my load. I have multiple lines that need to be checked.
Basically, if Prod =0 and Comm is blank and Reason is blank, do not load those lines after normalization. A sample output is marked in pink with what I do NOT want to see.
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 h:mm:ss[.fff] TT';
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];
/*Load IF (`Hr1_Prod` = 0 and `Hr1_Comm` = ' ' and `Hr1_Rsn` = ' ', hr1_prod and hr1_comm and hr1_rsn);*/
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,
Date(date#(right(date,6),'YYMMDD'),'MM/DD/YY')
Resident tabInput;
Generic LOAD
%ID,
Hour,
FieldNameNew,
FieldValue
Resident tabInput
Where FieldNameNew <> 'Hour';
DROP Table tabInput;
DisConnect;
the date in your excel is not in number format (qlikview format)
I suggest that you load your date field as such load num(dayStart(Date)) as Date
Table:
load num(dayStart(Date)) as Date....
Table2:
noconcatenate
load Date,.....
resident Table
where date > 41640
drop table TAble
this should work
You should be able to add
Hr1_Prod` = 0 and `Hr1_Comm` = ' ' and `Hr1_Rsn` = ' ', hr1_prod and hr1_comm and hr1_rsn
to your where clause in your SQL statement but change = to <>.
tabInput:
CrossTable (FieldName, FieldValue, 5)
LOAD RecNo() as %ID, *;
SQL
SELECT *
FROM `tbl_BoardHistoryNew`
WHERE Date > 41640 and Hr1_Prod` <> 0 and Len(Trim(Hr1_Comm) > 0 and Len(Trim(`Hr1_Rsn`) > 0;
Hello,
I tried this and got the following error:
SQL##f - SqlState: 37000, ErrorCode: 4294964196, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Date > 41640 and Hr1_Prod` <> 0 and Len(Trim(Hr1_Comm) > 0 and Len(Trim(`Hr1_Rsn`) > 0;
I fixed the syntax, but that gets rid of all lines. They would have to meet all three conditions, not just one.