topic Re: Left join/keep between resident tables in QlikView

Left join/keep between resident tables

Tue, 20 Mar 2012 11:43:08 GMT

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!!!!!

Left join/keep between resident tables

CELAMBARASAN — Tue, 20 Mar 2012 12:20:07 GMT

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

Re: Left join/keep between resident tables

Tue, 20 Mar 2012 12:25:58 GMT

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?

Re: Left join/keep between resident tables

CELAMBARASAN — Tue, 20 Mar 2012 12:31:12 GMT

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

Re: Left join/keep between resident tables

Tue, 20 Mar 2012 12:42:47 GMT

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.

Re: Left join/keep between resident tables

CELAMBARASAN — Tue, 20 Mar 2012 12:56:31 GMT

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

Re: Left join/keep between resident tables

CELAMBARASAN — Tue, 20 Mar 2012 12:58:45 GMT

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;

Re: Left join/keep between resident tables

Tue, 20 Mar 2012 13:09:17 GMT

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

];

Re: Left join/keep between resident tables

CELAMBARASAN — Tue, 20 Mar 2012 13:16:48 GMT

Hi,

Did you checked it in table View(Ctrl+T).

In Pivot table?What all the Expressions and dimensions you used in it?

Celambarasan

Re: Left join/keep between resident tables

Tue, 20 Mar 2012 14:03:39 GMT

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

Re: Left join/keep between resident tables

Luis_Cortizo — Tue, 20 Mar 2012 18:20:32 GMT

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

Re: Left join/keep between resident tables

Tue, 20 Mar 2012 21:13:41 GMT

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

Re: Left join/keep between resident tables

Luis_Cortizo — Wed, 21 Mar 2012 13:43:24 GMT

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

Re: Left join/keep between resident tables

Thu, 22 Mar 2012 08:08:09 GMT

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?