Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

nevilledhamsiri
Valued Contributor

Create a link & then bring in the values!

Dear Experts!

As per two tables joined which carries almost similar data except for few not agreed policy numbers,what I need is to show only disagreed numbers with their respective values in 2007. Also what is agreed need to be shown as agreed records with their respective values. I prefer this to be split in the script  so that  a list box could be created for agreed vs Not agreed

Thanks in advance

1 Solution

Accepted Solutions
Khan_Mohammed
Honored Contributor II

Re: Create a link & then bring in the values!

You are using P16 and P17

Where as in last table it is p16 and p17. Field names were not matching, case sensitive.

Try

2016:

LOAD POLICY_NUMBER AS P16, REF, RS

FROM (ooxml, embedded labels, table is [2016]);

Left Join

2017:

LOAD POLICY_NUMBER AS P17, REF, RS, D, E

FROM (ooxml, embedded labels, table is [2017]);

FinalLoad:

LOAD *,

if(P16 <> P17, 'Disagreed', 'Agreed') as Flag

Resident 2016;

drop Table 2016;

View solution in original post

15 Replies
johnhhtp2
Contributor II

Re: Create a link & then bring in the values!

Could you please elaborate more about your requirement like what is agreed and disagree and the condition in which differentiates both

nevilledhamsiri
Valued Contributor

Re: Create a link & then bring in the values!

Hi John!

Thanks for responding!

My requirement is something like this! As can be seen same policy numbers are shown in both 2016 & 2017 tables. I need This agreed policy numbers & disagreed policy numbers to be brought in o a list box so that I may be able to make appear agreed numbers on a click of this.

Thanks

Neville

t_chetirbok
Valued Contributor

Re: Create a link & then bring in the values!

Correct me if I'm wrong.

if policy number is in both files, it can be marked as Agreed. And if policy number is in only one file - Disagreed.

Khan_Mohammed
Honored Contributor II

Re: Create a link & then bring in the values!

How do you differentiate Agreed and Disagreed policy number?

Since all the fields are same and every field has a value....

Is there any certain amount where the policy is disagreed?

Khan_Mohammed
Honored Contributor II

Re: Create a link & then bring in the values!

Not sure if I understood but maybe...

2016:

LOAD POLICY_NUMBER, REF, RS,

if(POLICY_NUMBER=POLICY_NUMBER,'Agreed') as Agreed

FROM

(ooxml, embedded labels, table is [2016]);

Concatenate (2016)

2017:

LOAD POLICY_NUMBER, REF, RS,

if(POLICY_NUMBER=POLICY_NUMBER,'Disagreed') as Disagreed

FROM

(ooxml, embedded labels, table is [2017]);

nevilledhamsiri
Valued Contributor

Re: Create a link & then bring in the values!

Hi Tastiana,

Thanks for your responses & below is my exact requirement. How this will be done in the script?

If a policy number which is in 2016 table is appeared in 2017 as well, that record to be treated as "Agreed"  where as  records  (in 2017 table but not in 2016) need to be treated as disagreed. 

Thanks a lot

Neville

nevilledhamsiri
Valued Contributor

Re: Create a link & then bring in the values!

Hi S Khan,

What I need is if a policy number which is in 2016 is appeared in 2017 table as well, that record is agreed. If any record which is there in 2017 table but not in 2016 table that record to be treated as disagreed. Please help me to break this in the script.

Thanks for responding

Neville

Khan_Mohammed
Honored Contributor II

Re: Create a link & then bring in the values!

I guess below script?

2016:

LOAD POLICY_NUMBER as p16,

    REF,

    RS

FROM

(ooxml, embedded labels, table is [2016]);

left join

2017:

LOAD POLICY_NUMBER as p17,

    REF,

    RS

FROM

(ooxml, embedded labels, table is [2017]);

FinalLoad:

LOAD *,

if(wildmatch(p17 , '*'&p16&'*') or wildmatch(p16 , '*'&p17&'*'),'Agreed','Disagreed') AS Flag

Resident 2016;

drop Table 2016;

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

Or

2016:

LOAD POLICY_NUMBER as p16,

    REF,

    RS

FROM

(ooxml, embedded labels, table is [2016]);

left join

2017:

LOAD POLICY_NUMBER as p17,

    REF,

    RS

FROM

(ooxml, embedded labels, table is [2017]);

FinalLoad:

LOAD *,

if(p16 <> p17, 'Disagreed', 'Agreed') as Flag

Resident 2016;

drop Table 2016;

nevilledhamsiri
Valued Contributor

Re: Create a link & then bring in the values!

Dear S Khan,

An error message I am getting while data are loaded. (P16 file not found etc), If you don't mind could you please look in to this. If I am sent with the script you have written which will be helpful. Sorry for troubling.

Neville

Below is the script I have written.

2016:

LOAD POLICY_NUMBER AS P16, REF, RS

FROM (ooxml, embedded labels, table is [2016]);

Left Join

2017:

LOAD POLICY_NUMBER AS P17, REF, RS, D, E

FROM (ooxml, embedded labels, table is [2017]);

FinalLoad:

LOAD *,

if(p16 <> p17, 'Disagreed', 'Agreed') as Flag

Resident 2016;

drop Table 2016;