topic Re: Where condition with delimiter in QlikView

Where condition with delimiter

remo0017 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

How can i put a where condition in the load statement to pull out all the records between the delimiter '/'

Example:

/AAAAA/BBBBB/

/CCCC/DDDDD/

/EEEE/FFFFFF/

/GGGG/HHHHH/

Is there anything like /*/*/ something that can work ? which can pull out all the records irrespective what string lies between the delimiters ?

Something like

LOAD

from <filename>

where field1='/*/*/' or field1='/*/*/*/*/';

* pulling all records irrespective of which string is between the delimiters.

Thanks very much

Re: Where condition with delimiter

siddharth_s3 — Fri, 09 Feb 2018 18:52:10 GMT

SubField("YourColumn", '/' ,1) as FirstSet,

SubField("YourColumn", '/' ,2) as SecondSet

You can use any of this in your where clause.

Hope this is what you are looking for.

Re: Where condition with delimiter

remo0017 — Fri, 09 Feb 2018 19:06:24 GMT

Thanks Siddharth,

If my data set is like this

/AAAAA/BBBBB/

/EEEE/FFFFFF/

and if i use this

SubField("YourColumn", '/' ,1)

I would get firstset as AAAAA and second set as BBBBB right ?

I want to use any wildcads so i can get all the records irrespective of strings within the delimiters something like below

LOAD * from <table> where field1='/*/*/' or field1='/?/?/';

/*/*/

/AAAAA/BBBBB/

Re: Where condition with delimiter

siddharth_s3 — Fri, 09 Feb 2018 19:18:04 GMT

I don't think so there is a mixed magic special character search you are looking for

You have to replace your delimiters to a single one.

Something like: Replace(Replace(YourColumn, '?', '*'), '*', '/')

Basically this replaces all your Question Marks to * and Star to backslash

Then you can use your subfield like this

SUBFIELD(Replace(Replace(YourColumn, '?', '*'), '*', '/'), '/',1) as Firstset.

Your iterations of replace will increase with the number of keywords you have.

Re: Where condition with delimiter

remo0017 — Fri, 09 Feb 2018 19:23:42 GMT

To put it simple

select * from dept where dept='/IT/SOFTWARE/';

For the above query i will get all the records for the department /IT/SOFTWARE/ only.

But if there are any departments similar to like this, lets say /IT/HARDWARE/ than I would like to see them too without hardcoding the values like how i did in the SQL.

Any thoughts ?

Re: Where condition with delimiter

settu_periasamy — Fri, 09 Feb 2018 19:35:02 GMT

Try to use like or wildmatch.

LOAD * Where Test like '/*/*/';

LOAD * INLINE [
Test
/AAAAA/BBBBB/
/CCCC/DDDDD/
/EEEE/FFFFFF/
/GGGG/HHHHH/
Test123
Test/321
];

or

LOAD * Where wildmatch(Test,'/*/*/');

Re: Where condition with delimiter

MarcoWedel — Fri, 09 Feb 2018 19:39:46 GMT

where field1 like '/*/*/'

Re: Where condition with delimiter

Peter_Cammaert — Fri, 09 Feb 2018 19:44:01 GMT

For more details of Marco's solution, see: String operators ‒ QlikView

Re: Where condition with delimiter

remo0017 — Fri, 09 Feb 2018 19:46:11 GMT

I tried this and it doesn't work

Re: Where condition with delimiter

settu_periasamy — Fri, 09 Feb 2018 19:56:56 GMT

It is working for me. are you getting any error? or how your data exactly looks like?

can you provide the script what you have tried?

Re: Where condition with delimiter

remo0017 — Fri, 09 Feb 2018 19:57:35 GMT

Nopes that doesn't work too

Re: Where condition with delimiter

remo0017 — Fri, 09 Feb 2018 20:00:14 GMT

it gives all

/aaa/bbb/

/aaa/bbb/ccc/

/aaa/bbb/ccc/ddd/

/kkk/hhh/

I want to see only

/kkk/hhh/

/aaa/bbb/

Re: Where condition with delimiter

settu_periasamy — Fri, 09 Feb 2018 20:12:55 GMT

May be try like this.

LOAD * Where Test like '/*/*/' and SubStringCount(Test,'/')=3;

LOAD * INLINE [
Test
/aaa/bbb/
/aaa/bbb/ccc/
/aaa/bbb/ccc/ddd/
/kkk/hhh/
];

Re: Where condition with delimiter

shiveshsingh — Sat, 10 Feb 2018 02:38:26 GMT

Please try this

load * inline

[Field

/AAAAA/BBBBB/

/CCCC/DDDDD/

/EEEE/FFFFFF/

/GGGG/HHHHH/];

NoConcatenate

load PurgeChar(Field,'/') as Field1

Resident T;

Re: Where condition with delimiter

marcus_sommer — Sat, 10 Feb 2018 06:26:56 GMT

You could use wildmatch() for it:

LOAD

from <filename>

where wildmatch(field1, '/*/*/', '/*/*/*/*/');

- Marcus