Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Problem with conditions when loading table

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.

12 Replies
Not applicable

Problem with conditions when loading table

Hi Silviu,

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

Regards

lironbaram
Honored Contributor II

Problem with conditions when loading table

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

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

MVP
MVP

Problem with conditions when loading table

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) ;

OR

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

Regards,

Jagan.

Not applicable

Problem with conditions when loading table

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

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

Not applicable

Problem with conditions when loading table

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

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

Not applicable

Problem with conditions when loading table

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.

Community Browser