topic Problem with conditions when loading table in QlikView

Problem with conditions when loading table

Thu, 16 Feb 2012 09:51:46 GMT

Hello,

I have a small problem. I have imported a table, and the second table must be imported with some conditions regarding to the first one:

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

Pol1:

Load "POLICY_ID",
     "ANNEX_ID",
     "COVER_TYPE",
     "INSR_TYPE";
SQL select POLICY_ID, ANNEX_ID, COVER_TYPE, INSR_TYPE
from "..."."GEN_RISK_COVERED"
where RISK_STATE between 0 and 12
and ANNEX_ID=0;

for P=0 to NoOfRows('Pol1')-1;

LET vPOLICY_ID = Peek('POLICY_ID',$(P),Pol1);

LET vCOVER_TYPE =Peek('COVER_TYPE',$(P),Pol1);

LET vINSR_TYPE = Peek('INSR_TYPE',$(P),Pol1);

Pol2:

Load "POLICY_ID" as POLICY_ID2,
     "ANNEX_ID" AS ANNEX_ID2,
     "COVER_TYPE" AS COVER_TYPE2,
     "INSR_TYPE" AS INSR_TYPE2;
SQL select POLICY_ID, ANNEX_ID, COVER_TYPE, INSR_TYPE
FROM "INSIS_OMN"."GEN_RISK_COVERED"
where POLICY_ID=$(vPOLICY_ID)
and ANNEX_ID <>0
and COVER_TYPE<>$(vCOVER_TYPE)
and INSR_TYPE<>$(vINSR_TYPE)
and RISK_STATE between 0 and 12;

NEXT P;

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

The error that appear when reloading is:

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "PERSACCID": invalid identifier

SQL select POLICY_ID, ANNEX_ID, COVER_TYPE, INSR_TYPE
FROM "INSIS_OMN"."GEN_RISK_COVERED"
where POLICY_ID=100000018936
and ANNEX_ID <>0
and COVER_TYPE<>PERSACCID
and INSR_TYPE<>6016
and RISK_STATE between 0 and 12

Can someone detects what can be the problem?

It seems that I can't get this condition: COVER_TYPE<>$(vCOVER_TYPE)

where the first $(vCOVER_TYPE) is "PERSACCID" and it cannot be putted as a condition.

Thank you in advance,

Silviu.

Problem with conditions when loading table

Thu, 16 Feb 2012 10:06:38 GMT

Hi Silviu,

You have answered your problem already, you need quotes around that field

Regards

Problem with conditions when loading table

lironbaram — Thu, 16 Feb 2012 10:09:29 GMT

because PERSACCID is a text you need to change the sintax

you the line to look somethong like COVER_TYPE<>'PERSACCID'

so maybe you can do something in the varaible like

LET vCOVER_TYPE = chr(39) & Peek('COVER_TYPE',$(P),Pol1) & chr(39) ;

Problem with conditions when loading table

CELAMBARASAN — Thu, 16 Feb 2012 10:13:54 GMT

Hi,

Check with by using variable expression like this

LET vCOVER_TYPE =chr(39) & Peek('COVER_TYPE',$(P),Pol1)&chr(39);

LET vINSR_TYPE = chr(39) & Peek('INSR_TYPE',$(P),Pol1) & chr(39);

It may solve your problem

Celambarasan

Problem with conditions when loading table

jagan — Thu, 16 Feb 2012 10:16:36 GMT

Hi,

Need to include the Quotes before and after the Text value. You can use the expression suggested by for Variable as suggested Liron.

LET vCOVER_TYPE = chr(39) & Peek('COVER_TYPE',$(P),Pol1) & chr(39) ;

LET vCOVER_TYPE = '"' & Peek('COVER_TYPE',$(P),Pol1) & '"' ;

Regards,

Jagan.

Problem with conditions when loading table

Thu, 16 Feb 2012 10:31:49 GMT

Thank you for you're answers.

INSR_TYPE - is type NUMBER

POLICY_ID - is type NUMBER

COVER_TYPE - is type VARCHAR2

I have modify the following, I didn't got any errors, but 0 rows were imported:

LET vPOLICY_ID = chr(39)&Peek('POLICY_ID',$(P),Pol1)&chr(39);

LET vCOVER_TYPE =chr(39)&Peek('COVER_TYPE',$(P),Pol1)&chr(39);

LET vINSR_TYPE = chr(39)&Peek('INSR_TYPE',$(P),Pol1)&chr(39);

is there a conditioning error that I don't see?

Thx!

Silviu

Re: Problem with conditions when loading table

CELAMBARASAN — Thu, 16 Feb 2012 10:35:22 GMT

Hi,

number no need quotes.so use as below itself.Varchar only need quotes.

LET vPOLICY_ID = Peek('POLICY_ID',$(P),Pol1);

LET vCOVER_TYPE =chr(39)&Peek('COVER_TYPE',$(P),Pol1)&chr(39);

LET vINSR_TYPE = Peek('INSR_TYPE',$(P),Pol1);

Celambarasan

Yes your are right.Sorry I wrongly edited. Message was edited by: Celambarasan Adhimulam

Problem with conditions when loading table

Thu, 16 Feb 2012 10:47:42 GMT

Thank you for you're answer, but I thik that chr(39) must be on vCOVER_TYPE because it's a VARCHAR2 type.

I have tried

LET vPOLICY_ID = Peek('POLICY_ID',$(P),Pol1);

LET vCOVER_TYPE =chr(39)&Peek('COVER_TYPE',$(P),Pol1)&chr(39);

LET vINSR_TYPE = Peek('INSR_TYPE',$(P),Pol1);

but the error appear:

SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

SQL select POLICY_ID, ANNEX_ID, COVER_TYPE, INSR_TYPE
FROM "INSIS_OMN"."GEN_RISK_COVERED"

where POLICY_ID=
and ANNEX_ID <> 0
and COVER_TYPE<>''
and INSR_TYPE<>
and RISK_STATE between 0 and 12

Maybe vPOLICY_ID is not correctly populate...?

Re: Problem with conditions when loading table

CELAMBARASAN — Thu, 16 Feb 2012 10:51:57 GMT

Hi,

Use this as expression for the P instead of using For loop.

LET P=NoOfRows('Pol1')-1;

because it looks like problem with P variable only.

Celambarasan

Problem with conditions when loading table

Thu, 16 Feb 2012 10:56:22 GMT

Yes, but if I use it this way, it will take only the first record from table Pol1, the first POLICY_ID.

I have to make this verification for all POLICY_ID's that are in Pol1.

Problem with conditions when loading table

jagan — Thu, 16 Feb 2012 10:58:44 GMT

Hi,

By applying breakpoints check whether this variables are filled with values. The error occurred is due to empty values in the variable.

Regards,

Jagan.

Re: Problem with conditions when loading table

CELAMBARASAN — Thu, 16 Feb 2012 10:58:59 GMT

Hi,

Then there won't be a semicolon at the end of for P=0 to NoOfRows('Pol1')-1;

Remove the semi colon and try.

Celambarasan

Problem with conditions when loading table

Thu, 16 Feb 2012 11:28:59 GMT

I have tested my code into debug mode, and I observe that the first loop is ok (my variables get the values from the first row), but after my P increases and my variables must get the values from the seccond row, they are empty.

so is a problem with reading the first table regarding to the P row position.

when P is initial (=0), my variables are populated fine, but after P is increased, my variables are getting null.

Can you observe where is the mistake?

Thanx,

Silviu.