Forum Discussion
Align the column values with each other so you can easily see what's missing
How about this?
cli_one_dim~full_join_columns~0;
create temp table aa0 as
select 数量 content, group_concat(属性) file from full_join_columnsunion group by 数量;
create temp table aa as
select f01 from full_join_columns group by f01;
create temp table bb as
select f02 from full_join_columns group by f02;
create temp table cc as
select f03 from full_join_columns group by f03;
create temp table dd as
select f04 from full_join_columns group by f04;
select * from aa0 left join aa on content=f01 left join bb on content=f02 left join cc on content=f03 left join dd on content=f04
content file f01 f02 f03 f04
| *** Section1 | f01,f02,f03 | *** Section1 | *** Section1 | *** Section1 | |
| *** Section2 | f01,f02,f03,f04 | *** Section2 | *** Section2 | *** Section2 | *** Section2 |
| ****** ZZZControl.txt | f01 | ****** ZZZControl.txt | |||
| ****** ZZZFile1.txt | f02 | ****** ZZZFile1.txt | |||
| ****** ZZZFile2.txt | f03 | ****** ZZZFile2.txt | |||
| ****** ZZZFile3.txt | f04 | ****** ZZZFile3.txt | |||
| *Field 1 Data1 | f03,f04 | *Field 1 Data1 | *Field 1 Data1 | ||
| *Field 2 Data1, Data2, Data3 | f02 | *Field 2 Data1, Data2, Data3 | |||
| Field 1 | f01,f01,f02,f02,f03,f03,f04 | Field 1 | Field 1 | Field 1 | Field 1 |
| Field 2 | f01,f01,f02,f03,f03,f04 | Field 2 | Field 2 | Field 2 | Field 2 |
| Field 3 | f01,f01,f02,f02,f03,f04 | Field 3 | Field 3 | Field 3 | Field 3 |
if with hierarchical structure,maybe like this:
select * from full_join_columns;
create temp table aa as
select * from full_join_columns where rowid>1;
cli_one_dim~temp.aa~0;
create temp table bb as
select * from aaunion;
//select * from bb;
create temp table cc as
with recursive under_alice as (
select rowid old_rowid,属性,数量 s,0 f,0 d,数量
text from bb where rowid=1 union all
select a.rowid,a.属性,
case when instr(a.数量,'**')=1 then a.数量 else under_alice.s end,
case when instr(a.数量,'**')=1 then 0
when instr(a.数量,'Field')=1 then a.数量
else under_alice.f end f,
case
when instr(a.数量,'**')=1 then 0
when instr(a.数量,'Field')=1 then 0
when instr(a.数量,'*Field')=1 then a.数量 else 0 end,
a.数量 from bb a join under_alice on a.rowid=under_alice.old_rowid+1)
select 属性,s,f,d,text from under_alice;
//select * from cc;
cli_create_two_dim~cc~属性~text;
select colIdxf[3:]{iif(%s is null,highlight('x','yellow'),%s) %s} from cc_two_dim;
