Left join/keep between resident tables

[#1]

Hi all,

I have the table structured as follows:

[HYP]:

n client	n carrier	contract	policy	policy_short
11112222	253	abc	920/555	GLOBAL
11112222	253	abc	609/555	GLOBAL
11112222	253	ghi	258/555	GENERAL
11112222	8965	abc	920/555
66667777	253	abc2	920/555	GLOBAL
66667777	253	ghi2	875/555	GENERAL
44445555	4986	abc3	609/555
44445555	4986	abc3	920/555

The last column [policy_short] is computed as follows:

if([n carrier] = '253', if(left([policy], 3)='920', 'Global', if(left([policy], 3)='609', 'Global', 'General'))) as [policy_short]

From the above, I have created another table structured as follows:

[TEMP]:

n client	n carrier	Filter_Global
11112222	253	GLOBAL
66667777	253	GLOBAL

The script is:

LOAD [n client], [n carrier], 'GLOBAL' as [Filter_Global] resident [HYP] where [policy_short]='GLOBAL' and [n carrier] = '253';

Up to hear, great! (maybe I should put a group by somewhere above?)

If I load the script as it is, a Syn table is created between [n client] and [n carrier] on both tables, so that when I select 'GLOBAL' in the table [Filter_Global] I see all the contracts and policies belonging to those [n clients] that have at least one policy that has been marked as GLOBAL in the first table.

Next step is where I get stucked.

How can I build one unique table that holds all the values?

I'm looking for a table built as follows (I have dropped out the [policy_chort] field):

[HYP2]:

n client	n carrier	contract	policy	Filter_Global
11112222	253	abc	920/555	GLOBAL
11112222	253	abc	609/555	GLOBAL
11112222	253	ghi	258/555	GLOBAL
11112222	8965	abc	920/555
66667777	253	abc2	920/555	GLOBAL
66667777	253	ghi2	875/555	GLOBAL
44445555	4986	abc3	609/555
44445555	4986	abc3	920/555

Now, how do I do this??? I believe the solution is a left join, but I just can't seem to make it work. How should I build the join? Is it a join or a keep?

I have built something like:

[HYP2]:

left join (HYP)

load * resident TEMP;

but the above duplicates rows and figures I haven't understood based on what. (I haven't written all the fields, but I have very many fields in the HYP table)

Thanks!!!!!

[Luis_Cortizo · Accepted solution]

Well,

   For me, this kind of things are easier to see using a join.

If I reload using this code:

[hello]:

load

                    *

resident [HYP3];

left join (HYP3)

load

                    *

resident [temp];

//drop table [HYP3];

drop table [temp];

We don't drop the HYP3 table, because the first load is concatenating all the values... and we have the worst possible scenario.

Looking at a table object of the data we have this:

Codice  Capogruppo	Codice Gruppo	Numero Contratto	Polizza	polizza_short	premium	Z_Global
600672	000253	00156471	106.1006032621	106.1006032621	750
600672	000253	00156471	00000000061006032621	61006032621	-750
601349	000253	00175842	000000000000920A1521	920A1521	-19	Z-Global
601349	000253	00175842	000000000000920A1521	920A1521	19.151	Z-Global
601349	000253	00175842	000000000000920A1521	920A1521	6.500	Z-Global
601349	000253	00175842	000000000000920A1521	920A1521	1.295	Z-Global
601023	000253	00159645	000000000000AXH32132	AXH32132	904
602449	000253	00173802	000000000000920A8454	920A8454	235	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	42.573	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	356	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	-356	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	1.244	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	-1.912	Z-Global
600700	000253	00156819	000000000000209B7810	209B7810	120
600700	000253	00156830	000000000000209C0451	209C0451	113
600700	000253	00156843	000000000000209C1689	209C1689	111
034680	000253	20248328	920B1161	920B1161	3.272	Z-Global
004779	000253	20248818	000000000000209S9980	209S9980	404
004779	000253	20248818	000000000000209S9980	209S9980	-404
004779	000253	20248668	000000000000209U3340	209U3340	88
004779	000253	20248668	000000000000209U3340	209U3340	-88
602687	000253	00150666	000000000000920A4627	920A4627	0	Z-Global
008742	000253	00153519	000000000000209C2279	209C2279	91

23 possible rows, but the =count(total Polizza) is returning 156.

But...

Modifying this line of code:

HYP3:

load DISTINCT *,

          if([Codice Gruppo] = '000253', if(left([polizza_short], 3)='920', 'Global', if(left([polizza_short], 3)='609', 'Global', 'General'))) as [3_cifre_polizza_short]

resident [HYP2];

drop table [HYP2];

We have the same table object but a total count of Polizza of 23 (as it should).

So, what's happening?

Well, I don't still see it clearly but it seems that at any point, the synthetic key is messing the data before executing the join instruction.

A Distinct load should do it, but I believe that for your purposes it would be really much easier to do something like:

[HYP]:

load

          *,

          if([Codice Gruppo] = '000253', if(left(mid(Polizza,findoneof(Polizza, '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ\/')), 3)='920', 'Z-Global',

                    if(left(mid(Polizza,findoneof(Polizza, '123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ\/')), 3)='609', 'Z-Global'))) as [Z_Global] ;

load * inline [

Codice  Capogruppo	Codice Gruppo	Numero Contratto	Polizza	polizza_short	premium	Z_Global
600672	000253	00156471	106.1006032621	106.1006032621	750
600672	000253	00156471	00000000061006032621	61006032621	-750
601349	000253	00175842	000000000000920A1521	920A1521	-19	Z-Global
601349	000253	00175842	000000000000920A1521	920A1521	19.151	Z-Global
601349	000253	00175842	000000000000920A1521	920A1521	6.500	Z-Global
601349	000253	00175842	000000000000920A1521	920A1521	1.295	Z-Global
601023	000253	00159645	000000000000AXH32132	AXH32132	904
602449	000253	00173802	000000000000920A8454	920A8454	235	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	42.573	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	356	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	-356	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	1.244	Z-Global
602449	000253	00173802	000000000000920A8454	920A8454	-1.912	Z-Global
600700	000253	00156819	000000000000209B7810	209B7810	120
600700	000253	00156830	000000000000209C0451	209C0451	113
600700	000253	00156843	000000000000209C1689	209C1689	111
034680	000253	20248328	920B1161	920B1161	3.272	Z-Global
004779	000253	20248818	000000000000209S9980	209S9980	404
004779	000253	20248818	000000000000209S9980	209S9980	-404
004779	000253	20248668	000000000000209U3340	209U3340	88
004779	000253	20248668	000000000000209U3340	209U3340	-88
602687	000253	00150666	000000000000920A4627	920A4627	0	Z-Global
008742	000253	00153519	000000000000209C2279	209C2279	91

];

I believe that it gives you exactly the same result, don't need to do three auxiliary tables and can get rid of the keep/join part (I always try to avoid doing joins if possible because of this kind of things )

Hope it helps

[#3]

Thanks man! I think the distinct did it!

To make it harder, try adding the following line in the first load:

00156471,    100,    00000000092006032621,    000253,    600672

This case should be marked as Z-Global, since al least one other policy for that same Codice Capogruppo starts either with 920. The only way I found to do this is via 3 tables. Otherwise your're right: everything can be computed in one load. But this is not the case.

Let me know what you think. Thanks anyway

[Luis_Cortizo]

It should be marked as Z_Global just for that new line, right?.

I understand that the rest of rows with the same Numero Contratto should remain uncategorized.

Doing everything in one load with the preceding load seems to do what we could expect

Numero Contratto	premium	Polizza	Codice Gruppo	Codice Capogruppo	Z_Global
00156471	100	00000000092006032621	000253	600672	Z-Global

[#5]

Hi Luis,

no, not just that row. The logic is: if for all [polizza_short] belonging to one [Codice Capogruppo] there is al least one who's first 3 values are either 920 or 609, then the Z-Global tag needs to be applied to all of those policies.

On the other hand, if not even one [polizza_short] starts with either 920 or 609, then all policies belonging to that [Codice Capogruppo] should remain unmarked.

Suggestions?