Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have an issue . The problem that I faced is merging to data sets. To describe better;
Test ID | Active 1 | Active 2 | Active 3 | Active 4 |
2358 | 2.1 | 3.2 | 0 | 0.4 |
6541 | 3.3 | 1.2 | 5.7 | 0.2 |
Test ID | Active 1 | Active 2 | Active 3 | Active 4 |
2358 | Not tested | 0.3 | Not tested | Not tested |
6541 | Not tested | Not tested | 2.3 | Not tested |
Test ID | Active 1 | Active 2 | Active 3 | Active 4 |
2358 | 2.1 | 0.3 | 0 | 0.4 |
6541 | 3.3 | 1.2 | 2.3 | 0.2 |
Thank you in advance.
Regards
Mehmet
I would unpivot these tables, combine with your precedence logic, then pivot with generic load.
//unpivot source tables
table1:
crosstable (Active_Seq_tab1, Active_Score_tab1)
load *
;
load
*
from https://community.qlik.com/t5/QlikView-App-Dev/Data-merge/td-p/1845460
(html, utf8, embedded labels, table is @1)
;
table2:
crosstable (Active_Seq_tab2, Active_Score_tab2)
load *
;
load
*
from https://community.qlik.com/t5/QlikView-App-Dev/Data-merge/td-p/1845460
(html, utf8, embedded labels, table is @2)
;
//precedence of source tables
//start with table 2
final_table_pivot:
load
[Test ID] & [Active_Seq_tab2] as Test_Seq_Key,
[Test ID],
Active_Seq_tab2 as Active_Seq,
Active_Score_tab2 as Active_Score
resident table2
where Active_Score_tab2 <> 'Not tested'
;
//concatenate table 1 where not in table 2
concatenate(final_table_pivot)
load
[Test ID] & [Active_Seq_tab1] as Test_Seq_Key,
[Test ID],
Active_Seq_tab1 as Active_Seq,
Active_Score_tab1 as Active_Score
resident table1
where not exists (Test_Seq_Key, [Test ID] & [Active_Seq_tab1])
;
drop tables table1, table2;
//generic load
final_table_unpivot:
generic Load
[Test ID],
[Active_Seq],
[Active_Score]
resident final_table_pivot
order by [Test ID], [Active_Seq];
//Unpivot
set vListOfTables = ;
for vTableNo = 0 to NoOfTables()
let vTableName = Tablename($(vTableNo));
If Subfield(vTableName,'.',1)='final_table_unpivot' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
;
trace vTableName $(vTableName);
final_table:
load distinct
[Test ID]
resident final_table_pivot;
For each vTableName in $(vListOfTables)
Left Join (final_table) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName
drop tables final_table_pivot;
exit script;
@mhamurcuoglu , it can be done via incremental load (insert update) but for that some date field/other field is required which show the updated field value.
I would unpivot these tables, combine with your precedence logic, then pivot with generic load.
//unpivot source tables
table1:
crosstable (Active_Seq_tab1, Active_Score_tab1)
load *
;
load
*
from https://community.qlik.com/t5/QlikView-App-Dev/Data-merge/td-p/1845460
(html, utf8, embedded labels, table is @1)
;
table2:
crosstable (Active_Seq_tab2, Active_Score_tab2)
load *
;
load
*
from https://community.qlik.com/t5/QlikView-App-Dev/Data-merge/td-p/1845460
(html, utf8, embedded labels, table is @2)
;
//precedence of source tables
//start with table 2
final_table_pivot:
load
[Test ID] & [Active_Seq_tab2] as Test_Seq_Key,
[Test ID],
Active_Seq_tab2 as Active_Seq,
Active_Score_tab2 as Active_Score
resident table2
where Active_Score_tab2 <> 'Not tested'
;
//concatenate table 1 where not in table 2
concatenate(final_table_pivot)
load
[Test ID] & [Active_Seq_tab1] as Test_Seq_Key,
[Test ID],
Active_Seq_tab1 as Active_Seq,
Active_Score_tab1 as Active_Score
resident table1
where not exists (Test_Seq_Key, [Test ID] & [Active_Seq_tab1])
;
drop tables table1, table2;
//generic load
final_table_unpivot:
generic Load
[Test ID],
[Active_Seq],
[Active_Score]
resident final_table_pivot
order by [Test ID], [Active_Seq];
//Unpivot
set vListOfTables = ;
for vTableNo = 0 to NoOfTables()
let vTableName = Tablename($(vTableNo));
If Subfield(vTableName,'.',1)='final_table_unpivot' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
;
trace vTableName $(vTableName);
final_table:
load distinct
[Test ID]
resident final_table_pivot;
For each vTableName in $(vListOfTables)
Left Join (final_table) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName
drop tables final_table_pivot;
exit script;
Thanks @stevejoyce