topic Re: How to avoid the some rows coming in a particular column when pulling from the database in QlikView

How to avoid the some rows coming in a particular column when pulling from the database

Anonymous — Fri, 20 Jan 2017 16:39:20 GMT

Hi,

I have a table with many rows in a particular columsn.

My requirement is I have to avoid or fish out only particular rows from a particular column onto the qlivkiew table.

Wondering how can do I do that in the load script.

I am attaching the picture for your reference.

I want to have only the green color fields in the list box not the red colored one.

Any help is highly appreciated.

regards,

Vamsi.

Re: How to avoid the some rows coming in a particular column when pulling from the database

sunny_talwar — Fri, 20 Jan 2017 16:56:55 GMT

Do you only want to pull where attribute is 270 or 271?

Re: How to avoid the some rows coming in a particular column when pulling from the database

Anonymous — Fri, 20 Jan 2017 20:52:11 GMT

Yes.I only want those fields

Re: How to avoid the some rows coming in a particular column when pulling from the database

sunny_talwar — Fri, 20 Jan 2017 20:54:52 GMT

May be while loading...

LOAD ....

FROM .....

Where WildMatch(group_name, '*service-270*', '*service-271*');

Re: How to avoid the some rows coming in a particular column when pulling from the database

Anonymous — Sun, 22 Jan 2017 12:37:39 GMT

Hi sunny,

I have tried that but it is throwing error as unknown statement.

Re: How to avoid the some rows coming in a particular column when pulling from the database

sunny_talwar — Sun, 22 Jan 2017 12:50:50 GMT

Can you share a screenshot of the error and the script you have used?

Re: How to avoid the some rows coming in a particular column when pulling from the database

Anonymous — Wed, 25 Jan 2017 19:59:51 GMT

Hi sunny,

sorry for the late reply as i was out of the town.

I have tried with your wildmatch function at home using a sample data but didnt try at work.

I can attach the sample pic I have been using.

regards,

Vamsi

Re: How to avoid the some rows coming in a particular column when pulling from the database

sunny_talwar — Wed, 25 Jan 2017 22:11:15 GMT

Where statement goes after From

sample:

LOAD ....

FROM ....

WHERE ....;

Re: How to avoid the some rows coming in a particular column when pulling from the database

Anonymous — Wed, 25 Jan 2017 23:38:53 GMT

Thanks sunny It worked now.

I shall implement the same thing at work and will close this thread.

Much appreciated.

Re: How to avoid the some rows coming in a particular column when pulling from the database

Anonymous — Thu, 26 Jan 2017 10:41:26 GMT

Hi sunny,

I have used the same wildwatch function to pull the data but it is throwing error

cant able to make out where it went wrong as I have to use Two where conditions in my load script.

Here is the pic .and it is throwing these lines when i am loading the script.

SQL##f - SqlState: 42883, ErrorCode: 7, ErrorMsg: ERROR: function wildmatch(character varying, unknown, unknown) does not exist;

Error while executing the query

SQL SELECT *

, to_char(recorded_at,'Mon-YYYY') AS date_selector

, to_char(recorded_at,'YYYYMM') AS date_selector_1

FROM "myhealth_production"."public"."statistic_counters"

Where WildMatch(group_name, '*service-270*', '*service-271*')

Re: How to avoid the some rows coming in a particular column when pulling from the database

sunny_talwar — Thu, 26 Jan 2017 10:53:25 GMT

Since you are now doing this in the SQL, you need to use SQL equivalent of WildMatch which I think is using Like....

So something like this

SQL SELECT *

, to_char(recorded_at,'Mon-YYYY') AS date_selector

, to_char(recorded_at,'YYYYMM') AS date_selector_1

FROM "myhealth_production"."public"."statistic_counters"

Where group_name not in ('Conversation', 'Message') and

(group_name Like '*service-270*' or group_name Like '*service-271*');

May be you don't need the first statement now because you have more restrictive 2nd statement on group_name. So, this:

SQL SELECT *

, to_char(recorded_at,'Mon-YYYY') AS date_selector

, to_char(recorded_at,'YYYYMM') AS date_selector_1

FROM "myhealth_production"."public"."statistic_counters"

Where (group_name Like '*service-270*' or group_name Like '*service-271*');

Re: How to avoid the some rows coming in a particular column when pulling from the database

Anonymous — Thu, 26 Jan 2017 11:04:01 GMT

Hi sunny,

It is not throwing error but unless i use Where group_name not in ('Conversation', 'Message') statement , I am not getting any values in the group name. All those '*service-270*' or group_name Like '*service-271 data is inside the group name which are not in the 'conversation and message' .Dont know how to do this. dat

Re: How to avoid the some rows coming in a particular column when pulling from the database

stabben23 — Thu, 26 Jan 2017 11:14:30 GMT

in sql, use % instead of *

Re: How to avoid the some rows coming in a particular column when pulling from the database

Anonymous — Thu, 26 Jan 2017 11:40:28 GMT

Thanks Staffan .It worked fine now.

Re: How to avoid the some rows coming in a particular column when pulling from the database

Anonymous — Thu, 26 Jan 2017 11:41:29 GMT

Thanks sunny It worked fine now.

Great stuff.

Really appreicated.