Where condition with delimiter

[remo0017]

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

[siddharth_s3]

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.

[remo0017]

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/

[siddharth_s3]

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.

[remo0017]

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 ?

[settu_periasamy]

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,'/*/*/'); 

[MarcoWedel]

where field1 like '/*/*/'

[Peter_Cammaert]

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

[remo0017]

I tried this and it doesn't work

[settu_periasamy]

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?