Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Left join/keep between resident tables

Hi all,

I have the table structured as follows:

[HYP]:

n clientn carriercontractpolicypolicy_short
11112222253abc920/555GLOBAL
11112222253abc609/555GLOBAL
11112222253ghi258/555GENERAL
111122228965abc920/555
66667777253abc2920/555GLOBAL
66667777253ghi2875/555GENERAL
444455554986abc3609/555
444455554986abc3920/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 clientn carrierFilter_Global
11112222253GLOBAL
66667777253GLOBAL

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 clientn carriercontractpolicyFilter_Global
11112222253abc920/555GLOBAL
11112222253abc609/555GLOBAL
11112222253ghi258/555GLOBAL
111122228965abc920/555
66667777253abc2920/555GLOBAL
66667777253ghi2875/555GLOBAL
444455554986abc3609/555
444455554986abc3920/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!!!!!

1 Solution

Accepted Solutions
Luis_Cortizo
Former Employee
Former Employee

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  CapogruppoCodice GruppoNumero ContrattoPolizzapolizza_shortpremiumZ_Global
60067200025300156471106.1006032621106.1006032621750
600672000253001564710000000006100603262161006032621-750
60134900025300175842000000000000920A1521920A1521-19Z-Global
60134900025300175842000000000000920A1521920A152119.151Z-Global
60134900025300175842000000000000920A1521920A15216.500Z-Global
60134900025300175842000000000000920A1521920A15211.295Z-Global
60102300025300159645000000000000AXH32132AXH32132904
60244900025300173802000000000000920A8454920A8454235Z-Global
60244900025300173802000000000000920A8454920A845442.573Z-Global
60244900025300173802000000000000920A8454920A8454356Z-Global
60244900025300173802000000000000920A8454920A8454-356Z-Global
60244900025300173802000000000000920A8454920A84541.244Z-Global
60244900025300173802000000000000920A8454920A8454-1.912Z-Global
60070000025300156819000000000000209B7810209B7810120
60070000025300156830000000000000209C0451209C0451113
60070000025300156843000000000000209C1689209C1689111
03468000025320248328920B1161920B11613.272Z-Global
00477900025320248818000000000000209S9980209S9980404
00477900025320248818000000000000209S9980209S9980-404
00477900025320248668000000000000209U3340209U334088
00477900025320248668000000000000209U3340209U3340-88
60268700025300150666000000000000920A4627920A46270Z-Global
00874200025300153519000000000000209C2279209C227991

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  CapogruppoCodice GruppoNumero ContrattoPolizzapolizza_shortpremiumZ_Global
60067200025300156471106.1006032621106.1006032621750
600672000253001564710000000006100603262161006032621-750
60134900025300175842000000000000920A1521920A1521-19Z-Global
60134900025300175842000000000000920A1521920A152119.151Z-Global
60134900025300175842000000000000920A1521920A15216.500Z-Global
60134900025300175842000000000000920A1521920A15211.295Z-Global
60102300025300159645000000000000AXH32132AXH32132904
60244900025300173802000000000000920A8454920A8454235Z-Global
60244900025300173802000000000000920A8454920A845442.573Z-Global
60244900025300173802000000000000920A8454920A8454356Z-Global
60244900025300173802000000000000920A8454920A8454-356Z-Global
60244900025300173802000000000000920A8454920A84541.244Z-Global
60244900025300173802000000000000920A8454920A8454-1.912Z-Global
60070000025300156819000000000000209B7810209B7810120
60070000025300156830000000000000209C0451209C0451113
60070000025300156843000000000000209C1689209C1689111
03468000025320248328920B1161920B11613.272Z-Global
00477900025320248818000000000000209S9980209S9980404
00477900025320248818000000000000209S9980209S9980-404
00477900025320248668000000000000209U3340209U334088
00477900025320248668000000000000209U3340209U3340-88
60268700025300150666000000000000920A4627920A46270Z-Global
00874200025300153519000000000000209C2279209C227991

];

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

View solution in original post

13 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

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 applicable
Author

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?

CELAMBARASAN
Partner - Champion
Partner - Champion

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

Not applicable
Author

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.

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     You mean your table have duplicates like this in TEMP table

n clientn carrierFilter_Global
11112222253GLOBAL
66667777253GLOBAL
66667777253GLOBAL

Celambarasan

CELAMBARASAN
Partner - Champion
Partner - Champion

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;

Not applicable
Author

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

];

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

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

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

Celambarasan

Not applicable
Author

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