topic Re: Exists in QlikView

Exists

upaliwije — Tue, 16 Aug 2016 06:14:04 GMT

I have two data table in my QV load script.

Claim 1 and claim2

I want to load data from claim2 if a matching record exists in claim2 table. Matching field is RISK

I have written following script but it does not work correctly.

claim1:

LOAD VEHICLE_N0 AS RISK,

NOC,

PROVISON,

PAID

FROM

(ooxml, embedded labels, table is Sheet1);

load

POLICY_NO,

CLAIM_NO,

RISK,

PAID_AMOUNT

Resident CLAIM2

where Exists (RISK) ;

pls correct my syntax if it wrong

Re: Exists

crusader_ — Tue, 16 Aug 2016 06:19:19 GMT

Hi,

Syntax seems to be ok for this part of code but it's a bit unusual. Do you want to use Claim2 table further and separately with noname table? Have you ensured that they are not autoconcatenated?

Hope this helps.

Andrei

Re: Exists

Anonymous — Tue, 16 Aug 2016 06:27:38 GMT

what is done in the script before the part with claim1

any other load with a field named RISK?

then you should use another fieldname e.g. CLAIMRISK

or what is the Problem you are facing?

Re: Exists

upaliwije — Tue, 16 Aug 2016 06:30:17 GMT

Yes I want to use data deom claim2 table

Re: Exists

upaliwije — Tue, 16 Aug 2016 06:32:33 GMT

I load data from a sql first and the resulting table is CLAIM2. That is resident table

Re: Exists

Anonymous — Tue, 16 Aug 2016 06:33:42 GMT

can you post the Statement of your first SQL? does it contain a field named RISK?

Re: Exists

upaliwije — Tue, 16 Aug 2016 06:40:37 GMT

sql

select RISK

POLICY_NO,

CLAIM_NO,PAID_AMOUNT

from act_claim;

Re: Exists

crusader_ — Tue, 16 Aug 2016 06:43:20 GMT

What is the exact problem? Can you explain?

Have you checked that your tables Claim2 and 'Resident' did not concatenated?

/Andrei

Re: Exists

avinashelite — Tue, 16 Aug 2016 06:48:31 GMT

could you post your complete script ?? so that it will be helpful for us to debug the issue

Re: Exists

Anonymous — Tue, 16 Aug 2016 06:49:58 GMT

this may be the problem

use another field

claim1:

LOAD VEHICLE_N0 AS CLAIMRISK,

NOC,

PROVISON,

PAID

FROM

(ooxml, embedded labels, table is Sheet1);

load

POLICY_NO,

CLAIM_NO,

RISK as CLAIMRISK,

PAID_AMOUNT

Resident CLAIM2

where Exists (RISK,CLAIMRISK) ;

Re: Exists

upaliwije — Tue, 16 Aug 2016 06:56:25 GMT

CLAIM2:

sql

select RISK

POLICY_NO,

CLAIM_NO,

PAID_AMOUNT

from act_claim;

claim1:

LOAD VEHICLE_N0 AS RISK,

NOC,

PROVISON,

PAID

FROM

(ooxml, embedded labels, table is Sheet1);

load

POLICY_NO,

CLAIM_NO,

RISK,

PAID_AMOUNT

Resident CLAIM2

where Exists (RISK) ;

Re: Exists

upaliwije — Tue, 16 Aug 2016 07:16:51 GMT

Still not working

Re: Exists

avinashelite — Tue, 16 Aug 2016 07:22:40 GMT

try like this

CLAIM2:

sql

select RISK

POLICY_NO,

CLAIM_NO,

PAID_AMOUNT

from act_claim;

claim1:

LOAD VEHICLE_N0 AS C_RISK,

NOC,

PROVISON,

PAID

FROM

(ooxml, embedded labels, table is Sheet1);

noconcatenate

load

POLICY_NO,

CLAIM_NO,

RISK,

PAID_AMOUNT

Resident CLAIM2

where Exists (C_RISK,RISK) ;

Exists

Re: Exists

upaliwije — Tue, 16 Aug 2016 07:35:29 GMT

Thanks all