how to put a where clause in the script?

[giovannafurtadonunes]

Hello,

I have the code below, but the data in the table [TB_SERVICE_CALL] is still loaded when the SERVIÇO is equal to "OUTRO"

"" [TB_SERVICE_CALL_temp_f94abbfb-6659-39e9-1e34-f9b127a5]:
LOAD
[CD_SERVICE],
[NM_SERVICE],
[CD_CONTACT_TYPE],
[DS_CONTACT_TYPE],
[AB_CIFRA],
if ([NM_SERVICE] = '1º NIVEL CENTRAL CARTOES' or [NM_SERVICE] = '1 NIVEL CART PERDA ROUBO' or
[NM_SERVICE] = '1 NIVEL CART CONTESTACAO' or [NM_SERVICE] = '1 CART LIBERACAO LIMITES' or [NM_SERVICE] = 'CENTRAL DE CARTÕES'
or [NM_SERVICE] = 'CENTRAL DE CARTÕES NOVA' or [NM_SERVICE] = '1CENTRAL DE CARTÕES V2','CARTÕES BANCOOB',
if([NM_SERVICE] = '1º NIVEL SAC BANCOOB', 'SAC BANCOOB',
if([NM_SERVICE] = '1º NIVEL BNDES SICREDI','SICREDI',
if([NM_SERVICE] = '1º NIVEL CRESOL' OR [NM_SERVICE] ='CENTRAL CONFESSOL','CRESOL',
if([NM_SERVICE] = '1º NIVEL BNDES BRDE','BRDE',
if([NM_SERVICE]='PONTA - NIVEL 1'or [NM_SERVICE] = 'PONTA - NIVEL 2' or [NM_SERVICE] = 'PONTA - RETENCAO', 'PONTA',
if([NM_SERVICE]='1º NIVEL BLACK' or [NM_SERVICE]='1 NIVEL BLACK PERDA ROUBO' or [NM_SERVICE]='1 NIVEL BLACK CONTESTACAO'
or [NM_SERVICE]='1 BLACK LIBERACAO LIMITES' or [NM_SERVICE]='CENTRAL DE CARTÕES BLACK'
or [NM_SERVICE]='CENTRAL DE CARTÕES BLACK V2', 'CARTÕES BLACK',
if([NM_SERVICE]='1º NIVEL ESTABELECIMENTO', 'ESTABELECIMENTOS',
if([NM_SERVICE]='1º NIVEL COOPCERTO', 'COOPCERTO',
if([NM_SERVICE]='1º NIVEL YOOU', 'YOOU',
if([NM_SERVICE]='1º NIVEL SICOOB PREVI', 'PREVI',
if([NM_SERVICE]='1°NIVEL AC SIPAGUINHA', 'AC SIPAGUINHA',
if([NM_SERVICE]='1º NIVEL AC COOPCERTO', 'AC COOPCERTO',
if([NM_SERVICE]='1 NÍVEL APP SIPAG', 'APP SIPAG',
if([NM_SERVICE]='1°NÍVEL AC SIPAG', 'PA BANCOS/AC SIPAG',
if([NM_SERVICE]='1°NÍVEL SIPAGUINHA', 'SIPAGUINHA',
if([NM_SERVICE]='1 NIVEL SAC SIPAG', 'SAC SIPAG',
'OUTRO'))))))))))))))))) AS [SERVIÇO]

RESIDENT [TB_SERVICE_CALL];
DROP TABLE [TB_SERVICE_CALL];
RENAME TABLE [TB_SERVICE_CALL_temp_f94abbfb-6659-39e9-1e34-f9b127a5] to [TB_SERVICE_CALL];

LOAD *
RESIDENT [TB_SERVICE_CALL]
Where not Match([SERVIÇO], 'OUTRO'); ""

I also tried instead of XXXX this:

Where [SERVIÇO] <> 'OUTRO'

[danilostochi]

hello,

try.

RESIDENT [TB_SERVICE_CALL]
Where not Wildmatch( trim([SERVIÇO]), 'OUTRO'); ""

I also tried instead of XXXX this:

Where trim([SERVIÇO]) <> 'OUTRO'

[giovannafurtadonunes]

did not work

[martinpohl]

it's a difficult problem:

this script statement

LOAD *
RESIDENT [TB_SERVICE_CALL]
Where not Match([SERVIÇO], 'OUTRO'); 

will not generate a second table with filtered datas, ist will concatenate the filtered datas to the first table.

So a

noconcatenate load *

resident....

is nessesary 

😉

Regards

[giovannafurtadonunes]

Even with noncatenate, it didn't work, he created another table and it was added to the original.

I created a calculated column in the editor, took it out and did the filter again, it worked.

'

Unqualify *;

[TB_SERVICE_CALL_temp_458c22b2-0239-fa90-c2d9-7c11dfc0]:
LOAD
[CD_SERVICE],
[NM_SERVICE],
[CD_CONTACT_TYPE],
[DS_CONTACT_TYPE],
[AB_CIFRA],
[SERVIÇO]
RESIDENT [TB_SERVICE_CALL]
where [SERVIÇO] <> 'OUTRO';
DROP TABLE [TB_SERVICE_CALL];

RENAME TABLE [TB_SERVICE_CALL_temp_458c22b2-0239-fa90-c2d9-7c11dfc0] to [TB_SERVICE_CALL];'

[tm_burgers]

Create a new noconcatenate table and then drop the old table.

Or do a where wild match = all your conditions you put in your if statement which will exclude all your OUTRO entries.