Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
mhamurcuoglu
Contributor III
Contributor III

Data merge

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

Labels (2)
1 Solution

Accepted Solutions
stevejoyce
Creator III
Creator III

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;

 

 

 

stevejoyce_0-1634037991198.png

 

 

View solution in original post

3 Replies
Qlik1_User1
Creator III
Creator III

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

stevejoyce
Creator III
Creator III

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;

 

 

 

stevejoyce_0-1634037991198.png

 

 

View solution in original post

mhamurcuoglu
Contributor III
Contributor III
Author

Thanks @stevejoyce