etl script

[srinivasa1]

Hi

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.

[#2]

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"

If

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

[nagaiank]

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

[johnw · Accepted solution]

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);