topic etl script in QlikView

etl script

srinivasa1 — Thu, 29 Dec 2011 15:05:13 GMT

why bellow where condtion is not working if condtion is more then three in etl

where NAME<> 'A' OR NAME<> 'B' OR NAME<>'C'
OR NAME<>'D' OR NAME<>'E' OR NAME<>'F'
OR NAME<>'AB' OR NAME<>'GH';

My name filed is like this while loading trim(Upper(SubField(AssignedReviewer,';'))) as AssignedReviewer, is it creating any problem. pls guide me.

etl script

Thu, 29 Dec 2011 15:31:58 GMT

You should try to use the SubField Function with THREE Parameters (for details see documentation)

I assume you would have the first field?

trim(Upper(SubField(AssignedReviewer, ';', 1)))

Next your Statement seem to be "not working"

Name = 'X'

The condition is true, but also if

Name='A'

The condition is true (cause NAME <> 'B' is true an therefor at least one criteria is true and cause your chain is 'OR' one true is enough)

Which record you would have in your selection?

Which is the estimate result? All records with Name = 'A', 'B', 'C' ...

Or all records except these with Name = 'A', 'B', 'C' ... ?

Best regards

Michael

etl script

nagaiank — Thu, 29 Dec 2011 16:07:13 GMT

You may try any one of the following two scripts:

(1) Where Match(NAME,'A','B','C','D','E','F','AB','GH') = 0

(2) Substitute all the OR with AND in your script

Re: etl script

johnw — Thu, 29 Dec 2011 18:49:24 GMT

Think this one through and you'll see that EVERY name matches your original condition.

Name A: 'A' <> 'A' OR 'A' <> 'B' => false OR true => true
Name B: 'B' <> 'A' OR 'B' <> 'B' => true OR false => true
Name X: 'X' <> 'A' OR 'X' <> 'B' => true OR true => true

As for why it still isn't working with the match(), at the time you're doing your where, the name is still from the input, not from the subfield breakdown. I believe this would do the trick:

LOAD * WHERE not match(Name, 'HoldOutcomes', 'holdcharts', 'HoldMRA', 'HoldOutcomes', 'Invalid', 'InvalidReview', 'MRA', 'Outcomes');
LOAD
Name as tt
,subfield(Name,';') as Name
FROM test.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);