topic Data merge in QlikView

Data merge

mhamurcuoglu — Tue, 12 Oct 2021 11:02:00 GMT

Hi All,

I have an issue . The problem that I faced is merging to data sets. To describe better;

Data set 1 : This data set demonstrates first test results each ID

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

Data set 2: This data set demonstrates second test results of Active 2 for Test ID 2358 and second test results of Active 3 for Test ID 6541.

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

Merging data sets: I need a function to be able to update only if any active has a new result but if there is no second test performed to keep first test result.

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

Re: Data merge

Qlik1_User1 — Tue, 12 Oct 2021 11:19:48 GMT

@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.

Re: Data merge

stevejoyce — Tue, 12 Oct 2021 11:26:35 GMT

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;

Re: Data merge

mhamurcuoglu — Thu, 14 Oct 2021 06:26:39 GMT

Thanks @stevejoyce