topic Re: Edit Qlikview Script in QlikView

Edit Qlikview Script

Thu, 16 Aug 2012 11:16:25 GMT

Hi,

New to Qlikview and just come across my first hurdle! I am importing data from 2 sources, first source is from an ODBC source, second is from an Excel spreadsheet. The data imports fine however I want to restrict my import by adding a where clause to restrict the import, this was possible in Access with an inner join but obviously with relationships this isn't possible. Can I add a where clause in the edit script or do I import all of the data first and then add a condition somewhere in the expressions of the chart properties?

Help would be appreciated.

Thanks

Re: Edit Qlikview Script

Jason_Michaelides — Thu, 16 Aug 2012 11:21:55 GMT

You can add WHERE clauses in the script.

From ODBC:

LOAD

Field1

,Field2

,etc

;

SQL SELECT blah blah blah

FROM table WHERE blah blah blah;

From Excel:

LOAD

Field1

,Field2

,etc

FROM Excel...(ooxml stuff etc...)

WHERE blah blah blah;

Hope this helps,

Jason

Re: Edit Qlikview Script

Thu, 16 Aug 2012 12:25:28 GMT

Thanks Jason,

Yes this makes sense and answers half my question as in the place to add the clause is in the script editor. However my where clause is trying to add a condition that says where

field 1, table 1 (excel)

equals

field 1, table 1 (odbc)

In affect I am trying to join the two data sources on a common field to restrict the amount of data I bring back, hope that makes more sense?

Re: Edit Qlikview Script

Jason_Michaelides — Thu, 16 Aug 2012 12:44:21 GMT

QlikView will automatically join on all common fields (Field1 in this case). So, assuming you only have a single common field between the two data sources, and you only want to pull from the Excel source where data exists in the ODBC source, try this:

Table1:

LOAD

;

SQL SELECT * FROM Table...;

Table2:

LOAD * FROM Excel....

WHERE Exists(Field1);

Hope this helps,

Jason

Re: Edit Qlikview Script

Thu, 16 Aug 2012 13:32:47 GMT

Thanks Jason,

This doesn't seem to do anyhting, never mind I will have to carry on trying to figure out why it won't work. Very frustrating as it makes sense to me but I am still not getting a restricted data set when I run this.

Re: Edit Qlikview Script

Thu, 16 Aug 2012 13:41:20 GMT

Full script if it helps, I am trying to only display the numbers fromthe Closed .xls spreadsheet that match the numbers in the larger odbc numbers in the ""Prod".dbo.reporting" db

SET

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO [xyz];

//-------- Start Multiple Select Statements ------

SQL SELECT Sales, Invoice, Date

FROM "Prod".dbo.sales;

SQL SELECT Number, F as 'Sales'

FROM ""Prod".dbo.reporting;

//-------- End Multiple Select Statements ------

Closed:

LOAD Number
FROM

[..\..\Closed.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Exists (Number);

Re: Edit Qlikview Script

Jason_Michaelides — Thu, 16 Aug 2012 17:09:00 GMT

Try using a preceding load for your ODBC selects:

//-------- Start Multiple Select Statements ------

Table1:

LOAD

Sales,

Invoice,

Date

;

SQL SELECT Sales, Invoice, Date

FROM "Prod".dbo.sales;

Table2:

LOAD

Number,

Sales

;

SQL SELECT Number, F as 'Sales'

FROM ""Prod".dbo.reporting;

//-------- End Multiple Select Statements ------

Closed:

LOAD Number

FROM

[..\..\Closed.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Exists (Number);

Try that to see if it works, however if the only field you want is Number from the Excel sheet and actually you just want to flag ODBC records that are closed then ApplyMap() will probably suit you better.

Hope this helps,

Jason