Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!!!
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
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
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 |
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?