topic Re: filter results in load statement in QlikView

filter results in load statement

Tue, 23 Jun 2015 19:32:35 GMT

Hi, I'm new to Qlik, I am trying to filter out order type to only those coded as "O"

Seems logical to me that I could simply place in the load

"ord_type"='O'

But this does not seem to work, my results are pulling in O and Q type orders

I also tried "ord_type" like 'O'

Still no go

Re: filter results in load statement

sunny_talwar — Tue, 23 Jun 2015 21:50:28 GMT

Try this may be:

Order_Header:

LOAD yourListofFields,

...

Where MixMatch(order_type, 'O');

SQL SELECT *

FROM ...

Re: filter results in load statement

ramoncova06 — Tue, 23 Jun 2015 21:57:57 GMT

what results do you get by doing this in the db ?

Re: filter results in load statement

Wed, 24 Jun 2015 11:37:16 GMT

Still not working, but this this raises another question;

Placement of the where statement? If I place this at the bottom of the fields loaded but before the SQL Select the "Where statement turns red and doesn't seem to be an active function any longer, it turns blue if placed after the field but shows error in script editor.

Re: filter results in load statement

Wed, 24 Jun 2015 11:42:03 GMT

Not sure I understand the question, If I run an access query against the dbase I can easily filter out "Q" type orders with a field criteria of Like "O"

If I run SQL query same thing

Does Qlik use the same like or = functions as SQL? Also what does Qlik use for wildcard? * %

Re: filter results in load statement

sunny_talwar — Wed, 24 Jun 2015 12:00:45 GMT

You did not put the Where statement at the right place:

Order_Header:

LOAD "ord_type",

"ord_dt" as OrderDate,

Month("ord_dt") as Month,

Year("ord_dt") as Year,

"oe_po_no",

"user_def_fld_3" as Order_Code,

"cus_no",

"bill_to_name",

"ship_to_name",

"ship_to_addr_1",

"ship_to_addr_4",

"slspsn_no"

Where MixMatch ("ord_type", 'O');

SQL SELECT *

FROM "100".dbo."oeordhdr_sql";

Re: filter results in load statement

Wed, 24 Jun 2015 12:22:30 GMT

I did place the where statement as you show, like said, when doing this the where turns red and doesn't seem to be an active function.

Here is the script and the error.

Re: filter results in load statement

sunny_talwar — Wed, 24 Jun 2015 12:24:45 GMT

There is no comma (,) after "slspsn_no"

"slspsn_no"

Where MixMatch ("ord_type", 'O');

Re: filter results in load statement

Wed, 24 Jun 2015 12:30:34 GMT

I see now, sorry for the confusion. Works great.

Thank you so much!

Re: filter results in load statement

sunny_talwar — Wed, 24 Jun 2015 12:31:59 GMT

Not a problem

Re: filter results in load statement

jonathandienst — Wed, 24 Jun 2015 12:32:50 GMT

I would put the Where clause in he SQL statement, otherwise you are fetching the O's and Q's from the database over the network and into your QV server, and then discarding them.

SQL SELECT *

FROM "100".dbo."oeordhdr_sql"

Where ord_type = 'O';

Re: filter results in load statement

Wed, 24 Jun 2015 12:40:49 GMT

Perfect, that's what I was originally trying to do, but didn't know where to put the statement. Makes perfect sense.