Using multiple result sets served by SQL Server stored procedures

[#1]

Hi,

I'm fairly new to Qlikview (v11), and this issue has me stumped. I've searched previous answers, but none addresses my specific problem it seems.

I need to load two seperate result sets from an MS SQL Server, both which has to be fetched by a stored procedure (due some internal security issues). Both SPs are stored in the same database, so if possible, they might share a connection.

I believe I've tried all possible combinations of statement sequences in the load script, unfortunately without much success, the best result being having the latter result set being loaded, but not the former.

This is my basic approach, which doesn't work:

Sdata:

LOAD *;

ODBC CONNECT32 TO [NEC/GIS];

SQL EXEC spQlikviewData_S;

Pdata:

LOAD *;

ODBC CONNECT32 TO [NEC/GIS];

SQL EXEC spQlikviewData_P;

I've also tried building and calling a single SP, which returns both (two) result sets, but that only leaves me with one data set loaded, apparently.

I know that one can load virtually an unlimited number of datasets, so what's stumping me here ? Is there a limit on result sets fetched by stored procedures ?

Cheers

Lars

[maxgro]

// connect once, the db is the same

ODBC CONNECT32 TO [NEC/GIS];

Sdata:

LOAD *;

SQL EXEC spQlikviewData_S;

Pdata:

noconcatenate

LOAD *;

SQL EXEC spQlikviewData_P;

after the reload

open the table viewer ctrl+t

and verify if you have 2 tables with the correct number of records

[#3]

Thanks Massimo, it got me part of the way.

I do see two tables in the "Ctrl-T" browser, but they're still connected to each other and a third table named "$Syn 1 Table".

My two datasets (SData and PData) have a number of identical field/column names, but I only want to connect them via one field column ("Journalnummer"), if possible.

How can I accomplish that ?

And is there a reason why my reloading still freezes/hangs QV for many minutes ?

Cheers

Lars

[maxgro · Accepted solution]

Qlik associates by name of the field

So don't rename the Journalnummer field (Qlik will associate, you'll see a link in table viewer ctrl+t)

and rename (fieldname as newfieldname) the other fields with different names in the 2 tables (Qlik won't associate)

I suggest to start with few fields, below I used the field Journalnummer and 2 other fields (change it)

After reload I suppose you'll have only 2 tables with an association by Journalnummer

// connect once, the db is the same

ODBC CONNECT32 TO [NEC/GIS];

Sdata:

LOAD

    Journalnummer,                                  // join field, no rename

    field1 as field1S,                                  // no join, rename with different names in S and P

    field2 as field2S

    ;

SQL EXEC spQlikviewData_S;

Pdata:

noconcatenate

LOAD

    Journalnummer,                               

    field1 as field1P

    field2 as field2P

    ;

SQL EXEC spQlikviewData_P;

Regarding "$Syn 1 Table", when you have more than 1 common field between 2 tables, Qlik makes a syn table.

Synthetic Keys

And is there a reason why my reloading still freezes/hangs QV for many minutes ?

maybe because you have a lot of common fields and/or records in the 2 tables

[#5]

Thanks again Massimo,

As I wrote, I'm fairly new to this, so your tips did help me very much.

The data loads fast now, and the Ctrl-T now shows two tables with a single connection.

So, off to find other problems to hazzle over 🙂

Cheers

Lars