7 Replies Latest reply: Jan 5, 2018 1:59 PM by Katarzyna Wójcik RSS

    count the records in two tables association

    Katarzyna Wójcik

      I load into my app two tables which have one common field that joins them together - simply like that:

       

      d1:

      LOAD

      ID,

          d1.param1,

          d1.param2,

          d1.param3

      FROM [lib://qvd data/d1.qvd]

      (qvd);

       

      d2:

      LOAD

      ID,

          d2.param_a,

          d2.param_b

      FROM [lib://qvd data/d2.qvd]

      (qvd);

       

      Data in d1 contains the data with one row per each ID only, whereas d2 can have more rows with the same ID. The data from d1 and d2 is partly common, however there are some records in d1 which do not exist in d2 and the other way round. What I need is to calculate:

      1) how many records (distinct) are in d1 and d2

      2) how many records (distinct) are in d1 and not in d2

      3) how many records (distinct) are in d2 and not in d1


      I tried to add new field to each table with value = 1 ('1' as ' d1_bin', '1' as 'd2_bin') and I created a dimension which is based on concatenation of 1 or NULLs:


      =if(d1_bin = 1 and d2_bin = 1, 'd1&d2',

      if(d1_bin = 1 and IsNull(d2_bin ), 'd1 only',

          if(d2_bin = 1 and IsNull(d1_bin ), 'd2 only')))

       

      which gives the correct result I guess - looks like in the table it puts correct values according to the data. But if I want to select eg. 'd1 only', the selection that appears in the top bar includes d1=1, which actually gives both 'd1 only' and 'd1&d2'.


      Is there a way to fix it or do I miss an obvious solution for this? I've searched through the forum but couldn't really use the solutions posted..


      Another thing is that I cannot count distinct values of the tables - even if I put count(distinct ID), I am getting the same value as in count(ID) - and it's in fact the result of distinct count - no idea why and no idea how to check what could be the cause...?


      Thanks

      Kasia

        • Re: count the records in two tables association
          Mohammed Mukram Ali

          Hi,

           

          I have some solutions in my mind to implement.

          but can you prepare some dummy data and attach i will work on it .

           

          Thanks,

          Mukram.

            • Re: count the records in two tables association
              Mohammed Mukram Ali

              d1:

              LOAD

              ID,

                  d1.param1,

                  d1.param2,

                  d1.param3,

                   1 as d1_Flag

              FROM [lib://qvd data/d1.qvd]

              (qvd);

                   d2:

                   LOAD

                   ID,

                  d2.param_a,

                  d2.param_b,

                   1 as d2_Flag,

                   'Yes' as Flag

              FROM [lib://qvd data/d2.qvd]

              (qvd)

              Where Exists(ID);


                   d2:

                   LOAD

                   ID,

                  d2.param_a,

                  d2.param_b,

                   1 as d2_Flag,

                   'No' as Flag

              FROM [lib://qvd data/d2.qvd]

              (qvd)

              Where NOT Exists(ID);



              // Resident Load

              NoConcatenate

              Load *,

              if(d1_Flag=1 and  ISNULL(d2_Flag),'Only d1',

                 if(Flag='Yes','d1 & d2', 

                   if(Flag='No','d2'))) as Table_Value

              Resident d1;

              drop table d1;


              to count only from d1 table


              count(Distinct {<Table_Value={'Only d1'}>}ID)


              for in Both:

              count(Distinct {<Table_Value={'d1 & d2'}>}ID)


              for only d2:

              count(Distinct {<Table_Value={'d2'}>}ID)



              Thanks,

              Mukram,

            • Re: count the records in two tables association
              Bill Markham

              Maybe something like the attached.

                • Re: count the records in two tables association
                  Katarzyna Wójcik

                  @Bill,

                   

                  Thank you for the solution! I transformed it a little bit so that both tables are loaded from .qvd and then joined afterwards. It works! however the app is adding some synthetic keys while loading data... is that ok?

                   

                  Also, I'm wondering whether there is a chance to combine it into one field saying 'd1 only', 'd2 only', 'd1&d2' but that's just about to compress three filter panes into one, for now it's fine.

                   

                  What's interesting is that when selected 'd1&d2' to 'Yes' (so I get data existing in both tables), count and count distinct become to behave properly - does it mean that generally I cannot use regular count when having some divergent data?

                    • Re: count the records in two tables association
                      Bill Markham

                      If you add the bit in red italics below to my sample qvf then the new field [d Type]  should allow you to just use that for your single filter pane.

                       

                      Data:

                      NoConcatenate

                      Load

                        if ( [d1.Table] = 'd1' and isnull([d2.Table])

                        , dual('Yes',1) , dual('No',0) )  as [d1.Only] ,

                        if ( [d2.Table] = 'd2' and isnull([d1.Table])

                        , dual('Yes',1) , dual('No',0) )  as [d2.Only] ,

                        if ( [d2.Table] = 'd2' and [d1.Table] = 'd1'

                        , dual('Yes',1) , dual('No',0) )  as [d1.and.d2] ,

                           

                        if ( [d1.Table] = 'd1' and isnull([d2.Table])

                        , 'd1.Only' ,

                        if ( [d2.Table] = 'd2' and isnull([d1.Table])

                        , 'd2.Only' ,

                        if ( [d2.Table] = 'd2' and [d1.Table] = 'd1'

                        , 'd1.and.d2' ) ) ) as [d Type] ,

                        *

                      resident Temp ;


                      In my sample I only had one table, but if you have a synthetic key you must have one or more tables.  Double check that any temporary tables are deleted, in my example it does a Drop Table Temp ; at the end.