Trying to not load data that meets the conditions

[#1]

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;

[ali_hijazi]

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

[jpapador]

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;

[#4]

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;

[#5]

I fixed the syntax, but that gets rid of all lines. They would have to meet all three conditions, not just one.