Is it possible to use sql stored procedures in the qlik load script?

[#1]

We need to have a recursive seach on one of our tables. For this purpose we have a stored procedure on the SQL database.

Is it possible to use this stored procedure in a load script for Qlik view?

[marcus_sommer]

Yes, this should be possible then qlikview itself executed no SQL it will be only per odbc/oledb-driver to the database transferred and only this result received qlikview again. This meant it is depending from the correct driver and it's capabilities, see also: The Great ODBC Confusion

- Marcus

[jonathandienst]

Yes, you can use SP's in a load script. Here is a sample for a record set returning SP:

CostCenter:

LOAD *,

  RowNo() As RowID;

SQL EXECUTE QVGetData @Entity = 'CostCenter', @View = 'QRM';

The bold text is passed through to the DBMS for execution on the server and returns records to QV.

And here is an example that does not return records (and in that writes a log entry to the database from the load script):

LogStart:

SQL EXEC audit.spaEngineAuditStart

  @EngineName = 'EF Qlikview Engine',

  @WorkFlowRunID = 'SL$(zWorkFlowRunID)',

  @TgtName = 'Subledger',

  @UserName = '$(zOSUser)',

  @DataEffectiveDate = '$(zDataEffectiveDate)';

The variables are populated before this SQL statement is executed.

[#4]

Thanks for your supply.

I haven't been able to make it work for our specific needs.

[#5]

Hi Jonathan,

Thank you for your reply.

maybe i'm doing somthing wrong but i haven't been able to make it run.

[marcus_sommer]

I think you should post more details. How looked the script, what worked and what doesn't? Sometimes it's easier to perform such things within qlikview and not within the database - this meant at first to load pure tables from the database into qlikview and all the transformings happens in qlikview.

- Marcus

[#7]

Hi Marcus,

Thank you form you reply.

Please let me explain our needs;

We have a table with propeties with the folowing structure;

Property List ( hproplist) and a property (hproperty).

Each property can be member of more than one property list and each property can be another property list.

Let me explain with some examples;

Property list 943

hproplist	hproperty	scode
943	527	all-off
943	526	all-off
943	712	all-off
943	740	all-off

Property 527 is also a property list

hproplist	hproperty	scode
527	46	nl113
527	63	nl113
527	64	nl113
527	65	nl113
527	66	nl113

and property 526 also is another property list

hproplist	hproperty	scode
526	25	nl116
526	26	nl116
526	895	nl116

If we want to select the "All-off" property list in Qlik view we want all the properties, including those in property list 527, 526 and so on.

The loadscript is as folows;

ListProperty:

LOAD

LISTPROP_Property AS KEY_HPROP,

LISTPROP_PropList,

LISTPROP_Property,

LISTPROP_Scode;

SELECT distinct

1. pl.Hproplist LISTPROP_PropList,
2. pl.Hproperty LISTPROP_Property,
3. p.scode LISTPROP_Scode

FROM listProp pl

left join property p on pl.hproplist = p.hmy

where 1 = 1

group by pl.Hproplist,pl.hproperty,p.scode;

Any suggestions would much appreciated

Kind regards,

Rinus Reus

[marcus_sommer]

I'm not sure if this Unbalanced, n-level hierarchies fits directly to your task but I think it will give you some valuable hints.

- Marcus

[#9]

Hi Marcus,

This might be what I'm looking for, thanks.

Rinus