Splitting table into two tables based on condition

[berryandcherry6]

Hi,

i have a table

LOAD `dbid`,

    `comm_id`,

    `db_name`;

SQL SELECT `dbid`,

    `comm_id`,

    `db_name`

FROM DBtest;

dbid	comm_id	db_name
1	23	test1
2	23	test2
3	23	test3
4	24	test4
5	25	test5
6	26	test6
7	26	test7
8	26	test8

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

dbid	comm_id	db_name
1	23	test1
2	23	test2
3	23	test3
6	26	test6
7	26	test7
8	26	test8

Table2: which have single comm_id

dbid	comm_id	db_name
4	24	test4
5	25	test5

How could i do this?

[balar025]

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;

[pathiqvd]

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,

[sonkumamon]

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;