3 Replies Latest reply: Aug 8, 2017 2:17 AM by uri ophir RSS

    Splitting table into two tables based on condition

    Supriya R

      Hi,

       

      i have a table

       

      LOAD `dbid`,

          `comm_id`,

          `db_name`;

      SQL SELECT `dbid`,

          `comm_id`,

          `db_name`

      FROM DBtest;

       

      dbidcomm_iddb_name
      123test1
      223test2
      323test3
      424test4
      525test5
      626test6
      726test7
      826test8

       

       

      Here i have to join some tables to dbid when comm_id is greater than one,  If comm_id is one for single dbid then i have to join some others tables to particularly for single comm_id.


      Table1: which have multiple comm_id

       

      dbidcomm_iddb_name
      123test1
      223test2
      323test3
      626test6
      726test7
      826test8



      Table2: which have single comm_id

       

      dbidcomm_iddb_name
      424test4
      525test5

       

       

      How could i do this?

        • Re: Splitting table into two tables based on condition
          Ravi Balar

          Might this help.

          Data:

          LOAD dbid,

               comm_id,

               db_name

          FROM

          [https://community.qlik.com/thread/270433]

          (html, codepage is 1252, embedded labels, table is @1);

           

          Cnt:

          Load comm_id,

          count(comm_id) as Cnt

          Resident Data

          group by comm_id;

           

           

          Table11:

          Load comm_id,

          Cnt as Cnt1

          Resident Cnt

          where Cnt>=2;

           

           

          NoConcatenate

          Table22:

          Load comm_id,

          Cnt as Cnt2

          Resident Cnt

          where Cnt<2;

           

          Drop table Cnt;

          • Re: Splitting table into two tables based on condition
            lakshmipathi p

            Hi,

             

            Try This,

             

             

             

            table:

            LOAD `dbid`,

                `comm_id`,

                `db_name`;

            SQL SELECT `dbid`,

                `comm_id`,

                `db_name`

            FROM DBtest;

            left Join

            count:

            load   comm_id,

                      count(comm_id) as count_comm_id

            Resident table group by comm_id;

             

             

            table1:

            load   dbid as t1_dbid,

                      comm_id as t1_comm_id,

                     db_name as t1_db_name,

                     count_comm_id as t1_count_comm_id

            Resident budget where count_comm_id>1;

             

             

            table2:

            load    dbid as t2_dbid,

                      comm_id as t2_comm_id,

                     db_name as t2_db_name,

                     count_comm_id as t2_count_comm_id

            Resident budget where count_comm_id<=1;

             

            Regards,

            • Re: Splitting table into two tables based on condition
              uri ophir

              Try:

               

              map_aggr_comm_id:

              MAPPING

              LOAD

                   dbid

                   COUNT(comm_id)

              FROM DBtest

              GROUP BY dbid;


              Table_1:

              LOAD dbid,

                  comm_id,

                  db_name

              FROM DBtest

                   WHERE APPLYMAP('map_aggr_comm_id', dbid) > 1;


              Table_2:

              LOAD dbid,

                  comm_id,

                  db_name

              FROM DBtest

                   WHERE APPLYMAP('map_aggr_comm_id', dbid) = 1;