Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Trying to not load data that meets the conditions

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;

4 Replies
Highlighted
Partner
Partner

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

I can walk on water when it freezes
Highlighted
Partner
Partner

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;

Highlighted
Not applicable

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;

Highlighted
Not applicable

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