Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

Splitting table into two tables based on condition

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?

Tags (2)
3 Replies
balar025
Contributor III

Re: Splitting table into two tables based on condition

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
Contributor III

Re: Splitting table into two tables based on condition

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
New Contributor III

Re: Splitting table into two tables based on condition

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;


Community Browser