topic add some fields and populate them using a function. please help in QlikView

add some fields and populate them using a function. please help

Tue, 14 Feb 2012 17:21:09 GMT

Hello everyone,

I have a problem that must be solved urgently because it is applied on many tables, and the start is the hardest...

What I would like to do:

i have imported a table from a qvd file, and after that I would like to add a field (flag field) at the end of the table and to populate it with 1 or 0, regarding to my ORACLE function.

Is there a way that I can execute the oracle function directly for each line from my table, and to put the result into the flag field?

Each answer is precious.

Thank you,

Silviu.

add some fields and populate them using a function. please help

Tue, 14 Feb 2012 17:22:43 GMT

By any chance your qvd is populated from oracle?

add some fields and populate them using a function. please help

Tue, 14 Feb 2012 17:38:26 GMT

yes it is.

the oracle function that i would like to use calculates if a policy is changed or not and had been applied to the Oracle database.

the function is something like:

FUNCTION IS_ANNEX_CHANGED (p_policy_id number) return varchar is

cursor c1 is select count(*)

from(

select cfg.fract_type

from gen_risk_covered grc, cfg_gl_gen_fractions cfg

where grc.cover_type=cfg.cover_type ..................

etc...

the function returns Y for policies that are changed and N for others.

I would like to make execute the function into the load statement and to add a flag field that will contain the Y or N for each policy.

add some fields and populate them using a function. please help

Tue, 14 Feb 2012 17:42:42 GMT

Why dont you use a store procedure to populate a new table in oracle and qlikview extracts from there?

add some fields and populate them using a function. please help

Tue, 14 Feb 2012 17:49:56 GMT

Can you please detaliate what store procedure involves on my problem?

Thank you.

add some fields and populate them using a function. please help

Tue, 14 Feb 2012 18:00:16 GMT

I'm saying that maybe the best idea is to resolve your problem in the oracle bundle and only use qlikview to retrieve the modified information.

Create a store procedure in oracle that uses your function and populates a new table/view. Then only retireve from qv the info of the new table/view.

add some fields and populate them using a function. please help

Wed, 15 Feb 2012 13:57:39 GMT

Hello.

My function returns if a policy is changed or not.

What i would like to do is to load the POLICY table, to apply the function into the load script, and to add one field at the end of the table that will contain Y/N(changed/not changed) for each row(each policy ID).

Oracle function:

FUNCTION IS_ANNEX_CHANGED (p_policy_id number) return varchar is

cursor c1 is select count(*)

from

(

select cfg.fract_type

from gen_risk_covered grc, cfg_gl_gen_fractions cfg

where grc.cover_type=cfg.cover_type

and grc.insr_type=cfg.insr_type

and risk_state between 0 and 12

and grc.annex_id<>0

and policy_id=p_policy_id

minus

select cfg.fract_type

from gen_risk_covered grc, cfg_gl_gen_fractions cfg

where grc.cover_type=cfg.cover_type

and grc.insr_type=cfg.insr_type

--and risk_state between 0 and 12

and grc.annex_id=0

and policy_id=p_policy_id

);

nr number;

begin

open c1;

fetch c1 into nr;

close c1;

if nvl(nr,0)>0 then

return 'Y';

else return 'N';

end if;

end;

---------------------------------------------------------------------

Loaded table in qlikview:

policy:

LOAD POLICY_ID,

INSR_TYPE,

OFFICE_ID,

CLIENT_ID,

AGENT_ID,

INSR_BEGIN,

INSR_END

...................

FROM

[...\policy.qvd]

(qvd);

---------------------------------------------------------------------------

What I have made so far is:

POLICY:
LOAD
POLICY_ID,
INSR_TYPE,
OFFICE_ID,
CLIENT_ID,
AGENT_ID,
INSR_BEGIN,
INSR_END
FROM [...\policy.qvd] (qvd);

FOR P =0 to NoOfRows('POLICY')-1;

LET vPolicyID = Peek('POLICY_ID',$(P));

POLICY_ANNEX_CHANGED:
LOAD
'$(vPolicyID)' as POLICY_ID,
ANNEX_CHANGED;
SQL Select IS_ANNEX_CHANGED ($(vPolicyID)) as ANNEX_CHANGED from 'path';

NEXT P ;

After entering the function path, I receive the following error:

SQL##f - SqlState: S0002, ErrorCode: 942, ErrorMsg: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

Can someone give me any idea of what to do from here?

Thak you,

Silviu.