package

[Tool_Tip]

Dear all,

We have oracle package named as pack1.prod wherein we always pass one parameter as ID.

So, we have created our fact table in qliksense which has same ID and we need to pass this ID to this oracle package to get its respective values.

pack1.prod has column like ID, profit, margin so for each ID we have respective calculation on profit and margin now we want to display this profit and margin column to our fact table.

Fact table had ID column as well as some other dimensions tables.

So lets say we have 2000 unique ID into fact for which we need to get respective profit and margin by calling oracle package.

Kindly let us know how can we get it

[Kushal_Chawda]

@Tool_Tip  Create a data connection from Qlik sense to connect to oracle. You need to connect to oracle database with a user who has permissions to execute package from Qlik sense. If that setup is done, you can call oracle package from load script.

You can generate a loop in Qlik sense to call individual ID in package parameter

Data:
LOAD ID
FROM Fact;

For i=1 to FieldValueCount('ID')

let vFieldValueID = FieldValue('ID',$(i));

package_id:
SQL call pack1.prod ('$(vFieldValueID)');

Next

drop table Data;

[Kushal_Chawda]

@Tool_Tip  If you can pass the multiple comma separated values in parameter, you can do below

Data:
LOAD concat(chr(39) & ID & chr(39),',') as IDs
FROM Fact;

let vIDs= Peek('IDs',0,'Data');

package_id:
SQL call pack1.prod ($(vIDs));

drop table Data;

[Tool_Tip]

Hi Kushal,

Getting below error:

[Kushal_Chawda]

@Tool_Tip  try with

SQL exec pack1.prod ()

[Tool_Tip]

[Kushal_Chawda]

@Tool_Tip  can you share which script you are using?

[Tool_Tip]

[Tool_Tip]

Hi Kaushal,

I think it seems permission issue. The user running this package from qlik needs permission from database site.

[Kushal_Chawda]

@Tool_Tip  It seems permission issue only as I mentioned earlier that connection user should have permissions to execute package