Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Left Join
2017:
LOAD POLICY_NUMBER AS P17, REF, RS, D, E
FROM
FinalLoad:
LOAD *,
if(P16 <> P17, 'Disagreed', 'Agreed') as Flag
Resident 2016;
drop Table 2016;
Could you please elaborate more about your requirement like what is agreed and disagree and the condition in which differentiates both
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
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.
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?
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]);
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
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
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;
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
Left Join
2017:
LOAD POLICY_NUMBER AS P17, REF, RS, D, E
FROM
FinalLoad:
LOAD *,
if(p16 <> p17, 'Disagreed', 'Agreed') as Flag
Resident 2016;
drop Table 2016;