13 Replies Latest reply: Mar 22, 2012 4:08 AM by lollo figo RSS

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

        • Left join/keep between resident tables
          Celambarasan Adhimulam

          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

              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 Adhimulam

                  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

                      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 Adhimulam

                          Hi,

                               You mean your table have duplicates like this in TEMP table

                           

                          n clientn carrierFilter_Global
                          11112222253GLOBAL
                          66667777253GLOBAL
                          66667777253GLOBAL

                           

                          Celambarasan

                            • Re: Left join/keep between resident tables
                              Celambarasan Adhimulam

                              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

                                  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 Adhimulam

                                      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

                                          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

                                              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

                                                • Re: Left join/keep between resident tables

                                                  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

                                                      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

                                                          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?