topic Re: Loading Data from SQL Server in Connectivity & Data Prep

Loading Data from SQL Server

Fri, 11 Mar 2016 16:01:50 GMT

I am trying to replicate a dashboard that we have on another platform. I was able to import the data but I am having problems with parsing the data. How do I get Qlik to use the correct data? Thanx in advance for any help offered.

The fields that I'm using are:

R2MCU,

R2DCTO,

R2TQTY,

R2PSTB,

R2DTPT

and the SQL statement from the existing dashboard is:

SELECT DISTINCT

COUNT( DISTINCT R2URAB ) AS COLUMN0000,

COUNT( * ) AS COLUMN0001,

SUM( R2TQTY ) / 100 AS COLUMN0002

FROM

DCLINK.dbo.F4611 F4611

WHERE

R2TYFL = '2'

AND R2MCU = ' 20'

AND R2DTPT =(

SELECT

max( F4611.R2DTPT )

FROM

F4611 F4611

)

AND R2PSTB = '340'

AND R2DCTO NOT IN( 'SU', 'SY', 'SP', 'SR', 'S5', 'S1', 'S2', 'SE', 'SJ', 'SH', 'SZ' )

Re: Loading Data from SQL Server

Gysbert_Wassenaar — Fri, 11 Mar 2016 17:18:20 GMT

The fields you're loading in Qlik Sense are COLUMN0000, COLUMN0001 and COLUMN0002:

SELECT DISTINCT

COUNT( DISTINCT R2URAB ) AS COLUMN0000,

COUNT( * ) AS COLUMN0001,

SUM( R2TQTY ) / 100 AS COLUMN0002

FROM ...etc

If you want to use other fields then you have to load or create them first.

Re: Loading Data from SQL Server

Fri, 11 Mar 2016 17:57:04 GMT

Gysbert, Thank you for the input but how do I deal with the WHERE clause?

Re: Loading Data from SQL Server

Gysbert_Wassenaar — Fri, 11 Mar 2016 18:26:17 GMT

That's part of the sql statement and will be executed by the database server that Qlik Sense sends your sql statement to. Qlik Sense is not processing it. The database server deals with it. You don't have to.

Re: Loading Data from SQL Server

Fri, 11 Mar 2016 18:33:44 GMT

So, in this screen I would replace the plain SQL SELECT statement with my SQL statement, is that correct?

Thanks again for your help.

Re: Loading Data from SQL Server

Gysbert_Wassenaar — Fri, 11 Mar 2016 18:59:25 GMT

No, because your select statement does not create the fields listed in the preceding load as I mentioned earlier. Your sql statement will return the fields COLUMN0000, COLUMN0001 and COLUMN0002.