topic Re: How to remove those in QlikView

How to remove those

kulasekhar — Fri, 17 Apr 2015 08:38:53 GMT

Hi All,

Load the following data into qlikview:

LOAD * INLINE [
F1
0570035
0530065
0550099
0560016
0510015
0570010
0520040
0580002
];

Need to filter the data where the values contain the third digit ‘7’ marked in data above.

So output should be :

---------------
0530065
0550099
0560016
0510015
0520040
0580002

Thanks,

sekhar

Re: How to remove those

jonathandienst — Fri, 17 Apr 2015 08:43:46 GMT

In a LOAD:

LOAD ...

WHERE (Substr(F1, 3, 1) <> '7'

Or as an If expression:

SUM(If(Substr(F1, 3, 1) <> '7', F1))

HTH

Jonathan

Re: How to remove those

MK_QSL — Fri, 17 Apr 2015 08:52:54 GMT

LOAD

Where Index(F1,'7',1) <> 3;

LOAD TEXT(F1) as F1 INLINE

[

0570035

0530065

0550099

0560016

0510015

0570010

0520040

0580002

];

Re: How to remove those

kulasekhar — Fri, 17 Apr 2015 09:02:55 GMT

Thanks

Manish

Re: How to remove those

awhitfield — Fri, 17 Apr 2015 09:03:15 GMT

HI Kula,

try this please:

160553:
Load * Inline
[
F1
0570035
0530065
0550099
0560016
0510015
0570010
0520040
0580002
];

Results:
NoConcatenate Load
F1

Resident 160553
Where
Mid(F1,3,1)<>'7'
;

Drop table 160553;

Regards

Andy

Re: How to remove those

nagarjuna_kotha — Fri, 17 Apr 2015 09:04:16 GMT

Hi ,

Kind find below attached file .let me know its working to u or not ?

Regards,

Nagarjuna

Re: How to remove those

kulasekhar — Fri, 17 Apr 2015 09:13:43 GMT

1234567

1234576

1234756

1237456

1273456

1723456

7123456

How to remove All "7"

Re: How to remove those

awhitfield — Fri, 17 Apr 2015 09:20:12 GMT

160553:
Load * Inline
[
F1

1234567
1234576
1234756
1237456
1273456
1723456
7123456
];

Result:
NoConcatenate LOAD

PurgeChar(F1,7) as F1

Resident 160553;

Re: How to remove those

kulasekhar — Fri, 17 Apr 2015 09:24:51 GMT

doesn't work

first row 7 only remove

Re: How to remove those

kulasekhar — Fri, 17 Apr 2015 09:28:28 GMT

thanks but i don't have trial variation

Re: How to remove those

kulasekhar — Fri, 17 Apr 2015 09:30:32 GMT

sorry i have trial variation

Re: How to remove those

awhitfield — Fri, 17 Apr 2015 09:35:31 GMT

Works fine on mine, post what you have actually got in your script please

Andy

Re: How to remove those

jagan — Fri, 17 Apr 2015 09:39:21 GMT

Hi,

Try like this

To filter in script

LOAD

FROM DataSource

WHERE Mid(ID,3,1) <> '7';

If you want to handle in front end try like this

Sum(Aggr(If(Mid(ID,3,1) <> '7', 1, 0), ID))

Check this link below for similar one

Exclude values from count with set analysis

Regards,

Jagan.

Re: How to remove those

kulasekhar — Fri, 17 Apr 2015 09:40:06 GMT

Hi Andrew,

Load the following data into qlikview:

LOAD * INLINE[
F1

1234567

1234576

1234756

1237456

1273456

1723456

7123456

];

Need to filter the data where the values contain the digit ‘7’ in data above.

(remove 7 in that)

So output should be :

---------------

123456

Thanks,

sekhar

Re: How to remove those

awhitfield — Fri, 17 Apr 2015 09:44:41 GMT

This is the table view result of the above script I sent:

Andrew Whitfield 17-Apr-2015 05:20 (in response to kula sekhar)

160553:
Load * Inline
[
F1

1234567
1234576
1234756
1237456
1273456
1723456
7123456
];

Result:
NoConcatenate LOAD

PurgeChar(F1,7) as F1

Resident 160553;

Re: How to remove those

Marcellino_Groothof — Fri, 17 Apr 2015 09:46:01 GMT

Hi,

Try this:

temp:

Load * INLINE

[F1,
0570035,
0530065,
0550099,
0560016,
0510015,
0570010,
0520040,
0580002
];

Tabel:
LOAD * where Mid(dummy, 3, 1) <> '7';
LOAD F1 as dummy
Resident temp;

DROP Tables temp;

Greetings,

Marcellino

Re: How to remove those

vinod2086 — Fri, 17 Apr 2015 09:50:57 GMT

Hi Shekar,

Try this

Where not(WildMatch(F1,'057*'));

Re: How to remove those

kulasekhar — Fri, 17 Apr 2015 10:15:32 GMT

thanks

Andrew

Re: How to remove those

Peter_Cammaert — Fri, 17 Apr 2015 10:15:46 GMT

It may be a detail, but try to change the PurgeChar call in Andrews code into this:

PurgeChar(F1,'7') as F1

and even safer would be a call like htis:

PurgeChar(text(F1),'7') as F1

Re: How to remove those

kulasekhar — Fri, 17 Apr 2015 10:22:27 GMT

Thanks ever one