topic Re: Conditions while loading via Edit Script in QlikView

Conditions while loading via Edit Script

easternfish — Wed, 10 Jul 2013 15:35:26 GMT

Hi All,

Is this possible?....

LOAD

FILED1,

FIELD2,

FIELD3,

FIELD4,

IF(FIELD2 = 'X' And FIELD4 ='Y',0,1) AS FIELD42;

SQL SELECT *

FROM ....................WHERE FIELD42 > 0;

Thank you in advance!

Re: Conditions while loading via Edit Script

swuehl — Wed, 10 Jul 2013 16:33:16 GMT

No, FIELD42 is not known at the time the WHERE clause is executed. Either use the full condition in your SQL SELECT WHERE clause, or a preceding load with your new field:

LOAD * WHERE FIELD42 > 0;

LOAD

FILED1,

FIELD2,

FIELD3,

FIELD4,

IF(FIELD2 = 'X' And FIELD4 ='Y',0,1) AS FIELD42;

SQL SELECT *

FROM ....................;

But this will query the complete record set from your DBMS, then only limit the records on your QV client side.

Re: Conditions while loading via Edit Script

easternfish — Wed, 10 Jul 2013 17:03:47 GMT

Hi,

Can you please provide a sample on how to do it.

Thank you

Re: Conditions while loading via Edit Script

swuehl — Wed, 10 Jul 2013 22:25:33 GMT

Sorry, I don't quite understand. What about the script snippet in my previous post?

Re: Conditions while loading via Edit Script

easternfish — Thu, 11 Jul 2013 14:38:04 GMT

Oh sorry I didn’t notice.

LOAD * WHERE FIELD42 > 0;

Where would I input this information on a new TAB or same TAB above the SQL SELECT *.

Because I tried using it this way and it wouldn’t run.

LOAD * WHERE FIELD42 > 0;

LOAD

FILED1,

FIELD2,

FIELD3,

FIELD4,

IF(FIELD2 = 'X' And FIELD4 ='Y',0,1) AS FIELD42;

SQL SELECT *

FROM ....................;

Re: Conditions while loading via Edit Script

swuehl — Thu, 11 Jul 2013 14:47:44 GMT

You are getting an error when executing?

I think it should run, maybe I am missing something.

This should run as an example:

LOAD * WHERE Val >1;

LOAD Recno() as RecID, RAND()*3 as Val AutoGenerate 100;

But as said, it might be better to add the WHERE to the SQL, so you avoid to query too many records first hand:

LOAD

FILED1,

FIELD2,

FIELD3,

FIELD4,

IF(FIELD2 = 'X' And FIELD4 ='Y',0,1) AS FIELD42;

SQL SELECT *

FROM ....................WHERE FIELD2 = 'X' AND FIELD4 ='Y';

This should limit your records when querying the DB and you should only get a value of 1 in field FIELD42.

Re: Conditions while loading via Edit Script

easternfish — Thu, 11 Jul 2013 15:14:18 GMT

Ok Let me try it again. quick question I have this report running perfect in MS Access and I have multiple IIF and Criteria in the design followed by a multitude of Left Joins (for 10 different tables). What do you recommend would be the proper way to transition and move this report without having any mayor difficulties?

Thank you for all your help so far!!!!