topic Re: delete rows with special character in QlikView

delete rows with special character

Tue, 17 Mar 2015 08:14:12 GMT

Hi i Need your help

in my load script i want to delete the rows which have the characters Kostenstelle 1011747 Kostenart 2581500000 and [Konto Gegenbuchung] 10010

the way like i did it doesnt work really

here is my script

SAP_IST_F20:
LOAD Geschäftsjahr,
Periode,
AddMonths(makedate(Geschäftsjahr,2), Periode) as Date1,
// Month(Periode,+2) AS Month,
// Year(Date) AS Year,

Kostenstelle,
Kostenartengruppe,
Kostenart,
Kostenartenbeschr.,
[Konto Gegenbuchung],
[Bezeichnung des Gegenkontos],
[Wert/BWähr],
Berichtswährung,
Benutzername,
Buchungsdatum,
Bezeichnung,
[Nr Referenzbeleg],
Einkaufsbeleg
FROM

(qvd)
Where Kostenstelle <>1011747 and Kostenart <>2581500000 and [Konto Gegenbuchung]<> 10010; ????

STORE SAP_IST_F20 into Q:\10_QVD\SAP_IST_F20ohne1011746_1011746.qvd(qvd);

Re: delete rows with special character

Tue, 17 Mar 2015 08:17:26 GMT

Hi Andreas

try this

here num(Kostenstelle) <>'1011747' and num(Kostenart) <> '2581500000' and num([Konto Gegenbuchung]) <> '10010';

But this string tells the scrip not to load lines that match all 3 kriteria, If u what to string not load lines that have one of the kriteria u should use OR instead or AND.

/Teis

Re: delete rows with special character

Tue, 17 Mar 2015 08:20:23 GMT

i have the Problem that for example

Kostenstelle 1011756 and Kostenart 2581500000 and Konto Gegenbuchung 10010

are existing and then they are deleted too

can u help?

Re: delete rows with special character

Tue, 17 Mar 2015 09:11:00 GMT

Hi Andreas,

you are using an 'and' condition for your three criteria, is that how the data is represented?

Should the row you want to delete meet all three of the requirements, or are you actually wanting to delete any rows which meet have any of those criteria?

Your code at the moment looks fine if the row should contain all three.

If not and each should be separate, then an 'or' condition should be used.

Where Not (Kostenstelle ='1011747' Or Kostenart ='2581500000' or [Konto Gegenbuchung]= '10010')

hope that helps

Joe

Re: delete rows with special character

PrashantSangle — Tue, 17 Mar 2015 09:23:11 GMT

Hi,

Use Not match() or Not wildmatch() in where clause

try like

Where not Wildmatch(Kostenstelle,'*1011747*','*2581500000*') or not wildmatch([Konto Gegenbuchung],'*10010*')

use or/And as per requirement in abve expression

Regards

Re: delete rows with special character

Tue, 17 Mar 2015 10:08:23 GMT

hi Joe

thanx

can u explain me what i have to do when i have to do if i want to have this criteria

1. Kostenstelle 1011746 Kostenart 2581500000 Konto Gegenbuchung 10010

and the second criteria

2. Kostenstelle 1011746 Kostenart 9953000010

in 1. and 2. the criteria should be contain all three in 1. and both in 2.

can u help me?

Re: delete rows with special character

PrashantSangle — Tue, 17 Mar 2015 10:11:32 GMT

Hi,

did you apply my suggestion??

Regards

Re: delete rows with special character

Tue, 17 Mar 2015 10:15:28 GMT

hi Andrea

try this at the end of your load script

Where Not (Kostenstelle ='1011747' and Kostenart ='2581500000' and [Konto Gegenbuchung]= '10010') or

(Kostenstelle ='1011746' and Kostenart ='995300010' )

/Teis

Re: delete rows with special character

Tue, 17 Mar 2015 10:23:31 GMT

Hi Teis,

for the second criteria it doesnt work

can u help again?

Re: delete rows with special character

Tue, 17 Mar 2015 10:25:48 GMT

Hi Andreas,

Where Not ((Kostenstelle ='1011747' and Kostenart ='2581500000' and [Konto Gegenbuchung]= '10010') or

(Kostenstelle ='1011746' and Kostenart ='995300010' ))

should do that for you

Joe

Re: delete rows with special character

Tue, 17 Mar 2015 10:28:43 GMT

Hi Andreas

Maybe the second cratieria also should have the KONTO? if so just add it from the line above

/Teis

Re: delete rows with special character

Tue, 17 Mar 2015 10:33:42 GMT

No i dont Need Konto Gegenbuchung in the second line the field here is empty

when i write the second criteria at the first then it works not for the first criteria

i think its only a Little Thing why it doesnt work

Re: delete rows with special character

pavendhan — Tue, 17 Mar 2015 10:41:31 GMT

Hi,

Use Not wildmatch(Kostenstelle,'')

Re: delete rows with special character

Tue, 17 Mar 2015 11:14:02 GMT

Hi,

Try this condition and put the single quotes.

Where Kostenstelle <> '1011747' and Kostenart <> '2581500000' and [Konto Gegenbuchung] <> '10010' ;

Re: delete rows with special character

cleitoncabral2 — Tue, 17 Mar 2015 11:26:37 GMT

SAP_IST_F20:
CARGA Geschäftsjahr ,
Periode ,
AddMonths ( makedate ( Geschäftsjahr , 2), Periode ) como Date1 ,
// Month (Periode, + 2) como o mês,
// Ano (Data) como o ano,

Kostenstelle ,
Kostenartengruppe ,
Kostenart ,
Kostenartenbeschr. ,
[Konto Gegenbuchung] ,
[Bezeichnung des Gegenkontos] ,
[Wert / BWähr] ,
Berichtswährung ,
Benutzername ,
Buchungsdatum ,
Bezeichnung ,
[Nr

Onde Kostenstelle <> ' 1011747 ' e Kostenart <> ' 2581500000 ' e [Konto Gegenbuchung] <> ' 10010 ' ;

STORE SAP_IST_F20 em Q: \ 10_QVD \ SAP_IST_F20ohne1011746_1011746.qvd ( QVD );