Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

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

Merging records in Qlik View Scripting by the same key

Hello community!

I hope for a suggestion for the following challenge in Qlik Scripting Coding. I am a Beginner and I think, this topic is pretty common in Qlik Scripting.

We have following input file called PA0521TZ.qvd:

PERNR;%key;BEGDA;ATZPH
00250731;873-00250731;01.01.2011;BA
00250731;873-00250731;17.12.2012;BF
00250846;873-00250846;01.10.2014;BA
00250846;873-00250846;01.10.2015;BF
00250846;873-00250846;01.10.2016;BA
00579002;873-00579002;01.03.2017;BA
00579002;873-00579002;26.01.2018;BF

As we can see for the PERNR, for example 00250731 we have two records. They differ in the values of the columns BEDA and ATZPH. Our goal is to produce a new record out of the two which should look as follows

PERNR; %key; BAField; BFField

00250731;873-00250731;01.01.2011;17.12.2012

So it is a kind of merging of any to records that have the same key.

I was suggested to use apply_map... but this does not work. Any suggestions would be appreciated.

 

Kind Regards, Alexandru Draghici

 

Alexandru Draghici
BICC at Komm.ONE
2 Solutions

Accepted Solutions
tresesco
MVP
MVP

You can try with Generic Load, like:

Generic Load
//PERNR &'|'& %key as KEY, ATZPH,BEGDA   //you can use the KEY idea to avoid synthetic key issue
PERNR , %key , ATZPH,BEGDA
Inline [
PERNR;%key;BEGDA;ATZPH
00250731;873-00250731;01.01.2011;BA
00250731;873-00250731;17.12.2012;BF
00250846;873-00250846;01.10.2014;BA
00250846;873-00250846;01.10.2015;BF
00250846;873-00250846;01.10.2016;BA
00579002;873-00579002;01.03.2017;BA
00579002;873-00579002;26.01.2018;BF] (delimiter is ';')

tresesco_0-1619595480682.png

Note: you can even leave the synthetic keys as is unless this is being impacted by other tables in your data model.

View solution in original post

draghici1109
Contributor III
Contributor III
Author

Yes, this is the solution... thank you for your timely response!

I used generic load in the past in other situations, yet did not realized that this is also an use case!

Honored to become part of such a high professional community!

Kind Regards from Germany

A. Draghici

Alexandru Draghici
BICC at Komm.ONE

View solution in original post

2 Replies
tresesco
MVP
MVP

You can try with Generic Load, like:

Generic Load
//PERNR &'|'& %key as KEY, ATZPH,BEGDA   //you can use the KEY idea to avoid synthetic key issue
PERNR , %key , ATZPH,BEGDA
Inline [
PERNR;%key;BEGDA;ATZPH
00250731;873-00250731;01.01.2011;BA
00250731;873-00250731;17.12.2012;BF
00250846;873-00250846;01.10.2014;BA
00250846;873-00250846;01.10.2015;BF
00250846;873-00250846;01.10.2016;BA
00579002;873-00579002;01.03.2017;BA
00579002;873-00579002;26.01.2018;BF] (delimiter is ';')

tresesco_0-1619595480682.png

Note: you can even leave the synthetic keys as is unless this is being impacted by other tables in your data model.

View solution in original post

draghici1109
Contributor III
Contributor III
Author

Yes, this is the solution... thank you for your timely response!

I used generic load in the past in other situations, yet did not realized that this is also an use case!

Honored to become part of such a high professional community!

Kind Regards from Germany

A. Draghici

Alexandru Draghici
BICC at Komm.ONE

View solution in original post