Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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,
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;