topic Merging records in Qlik View Scripting by the same key in QlikView

Merging records in Qlik View Scripting by the same key

draghici1109 — Wed, 28 Apr 2021 07:02:52 GMT

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

Re: Merging records in Qlik View Scripting by the same key

tresB — Wed, 28 Apr 2021 07:39:30 GMT

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 ';')

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

Re: Merging records in Qlik View Scripting by the same key

draghici1109 — Wed, 28 Apr 2021 07:55:17 GMT

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