Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

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
MK9885
Master II
Master II

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
Anonymous
Not applicable

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

nevilledhamsiri
Specialist
Specialist
Author

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
Creator III
Creator III

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.

MK9885
Master II
Master II

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?

MK9885
Master II
Master II

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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

MK9885
Master II
Master II

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
Specialist
Specialist
Author

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;