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
Hi,
Here Left join will add the column to the HYP table only which you have specified in syntax left join(HYP).
If you need it has separate table then you need to load it once again and then join with it.
I think Synthetic key will be formed between HYP and TEMP and HYP2 tables?Qlikview automatically forms synthetic key based on more than one column is common between 2 tables.
HYP:
Load
From Datasource;
Left Join(HYP)
Load
From Datasource;
This makes your process easier.
Celambarasan
Not sure I understrood. The two tables HYP and TEMP are already computed separately.
The problem is when I try to join the two: I can't seem to apply the combination [n client] & [n carrier] in the TEMP table to all the occurances in the HYP table.
How do I do that in order to obtain as an outcome just one table wich contains all the values?
Hi,
Have you tried like this it will give you a single table with all values
Use a left join between the two tables that you loaded from database.If you don't understand post your script.
HYP:
Load
nclient,
ncarrier,
.....
From HYPTablesource;
Left Join(HYP)
Load
nclient,
ncarrier
.....
From TempTablesource;
Celambarasan
This is partially working: true I get a single table that seemt to be connected how I want.
But on the other side I get repetitions. In other words, the rows seems to be multiplied as many times as the number of occurances for each client found. In other words: QV is not applying the TAG but is repeating the rows in HYP as many times as they appear in the TEMP table.
Hi,
You mean your table have duplicates like this in TEMP table
n client | n carrier | Filter_Global |
---|---|---|
11112222 | 253 | GLOBAL |
66667777 | 253 | GLOBAL |
66667777 | 253 | GLOBAL |
Celambarasan
Hi,
It gives the exact solution you looking for?
LOAD * Inline
[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];
left join
Load * Inline
[n client,n carrier,Filter_Global
11112222,253,GLOBAL
66667777,253,GLOBAL
];
DROP Field policy_short;
Oh yes, this is the case. I have addes a value at the end of the first load.
If you look at the value of the single contract in a pivot table, you will see it is multiplied.
LOAD * Inline
[n client,n carrier,contract,policy,policy_short, value
11112222,253,abc,920/555,GLOBAL, 10
11112222,253,abc,609/555,GLOBAL, 10
11112222,253,ghi,258/555,GENERAL, 10
11112222,8965,abc,920/555, , 10
66667777,253,abc2,920/555,GLOBAL, 10
66667777,253,ghi2,875/555,GENERAL, 10
44445555,4986,abc3,609/555, , 10
44445555,4986,abc3,920/555, , 10
];
left join
Load * Inline
[n client,n carrier,Filter_Global
11112222,253,GLOBAL
66667777,253,GLOBAL
];
Hi,
Did you checked it in table View(Ctrl+T).
In Pivot table?What all the Expressions and dimensions you used in it?
Celambarasan
Hi,
I'm loading a little example of what I have. Can't load all because it's sensible info.
I have a personal edition, so I'm not sure I'll be able to open the file if you send it back. Just post the script trick if you can figure it out.
Thanks for your time