topic Re: How to go from several rows per ID to one row per ID with several columns in load script? in App Development

How to go from several rows per ID to one row per ID with several columns in load script?

blipblopdk — Wed, 19 Nov 2025 09:35:41 GMT

Hi there,

In Qlik Sense load script, how can I convert the data in the rows and columns in the Excel-file into the rows and columns that I want? Please see inserted picture (also attached).

The data below is just an example. The actual data set is much bigger so I need an automated way of handling the conversion.

BR Kim

Re: How to go from several rows per ID to one row per ID with several columns in load script?

Daniel_Castella — Thu, 20 Nov 2025 10:35:45 GMT

Hi @blipblopdk

It is probably not the fanciest code, but it seems to work. Let me know if it works for you:

LOAD Initials, UDD, GEO

INLINE [

Initials, UDD, GEO

KKK, Lurer, Aar

KKK, Lurer, Sil

AAA, Purer, Aar

AAA, Purer, Hor

AAA, Murer, Ran

LLL, Kurer, Sil

];

LOAD

DISTINCT Initials

RESIDENT A;

FOR i = 0 TO FieldValueCount('Initials') - 1

LET vValue = FieldValue('Initials', i + 1);

LOAD Count(Initials) as Loop

RESIDENT A

Where Initials='$(vValue)';

LET vCounter = peek('Loop', 0, 'C');

FOR j = 1 TO '$(vCounter)'

D :

LOAD UDD

RESIDENT A

Where Initials='$(vValue)' ;

LET vUDD = peek('UDD', j-1, 'F');

F_$(j):

LOAD

'$(vValue)' as Initials,

'$(vUDD)' as UDD_$(j)

AutoGenerate 1;

FOR k = 1 TO '$(vCounter)'

LOAD GEO

RESIDENT A

Where Initials='$(vValue)';

LET vGEO = peek('GEO', k-1, 'G');

H_$(k):

LOAD

'$(vValue)' as Initials,

'$(vUDD)' as UDD_$(k),

'$(vGEO)' as GEO_$(k)

AutoGenerate 1;

DROP TABLE G;

NEXT k

DROP TABLE D;

NEXT j

DROP TABLE C;

NEXT i

LOAD

Initials,

Count(UDD) as Loop2

RESIDENT A

GROUP BY Initials;

LOAD max(Loop2) as Loop2

RESIDENT I;

LET vLoop2 = peek('Loop2', 0, 'J');

FOR l = 1 TO '$(vLoop2)'

LEFT JOIN(B)

LOAD *

RESIDENT F_$(l);

DROP TABLE F_$(l);

NEXT l

FOR m = 1 TO '$(vLoop2)'

LEFT JOIN(B)

LOAD *

RESIDENT H_$(m);

DROP TABLE H_$(m);

NEXT m

NoConcatenate

LOAD *

RESIDENT B

Where len(UDD_1) > 0 and len(GEO_1) > 0;

DROP TABLE A, B, I, J;

Kind Regards

Daniel

Re: How to go from several rows per ID to one row per ID with several columns in load script?

blipblopdk — Thu, 20 Nov 2025 11:27:51 GMT

Hi Daniel,

Thank you SO very much. It works flawlessly in my end. I have more columns in my original data set, but I think that I can probably work out from your code how to make that work, also.

BR Kim

Re: How to go from several rows per ID to one row per ID with several columns in load script?

Nagaraju_KCS — Thu, 20 Nov 2025 11:43:56 GMT

You can other way using by Generic Load

TempLoad1:
LOAD * INLINE [
Initials, UDD, GEO
KKK, Lurer, Aar
KKK, Lurer, Sil
AAA, Purer, Aar
AAA, Purer, Hor
AAA, Murer, Ran
LLL, Kurer, Sil
];

TempLoad:
Load
Initials,
UDD,
GEO,
If(Initials = Peek('Initials'), Peek('RowCounter') + 1, 1) AS RowCounter
ORDER BY Initials;

Drop table Tempload1;

StackTable:
LOAD
Initials,
RowCounter & '_UDD' AS FieldName,
UDD AS FieldValue
RESIDENT TempLoad;

CONCATENATE (StackTable)
LOAD
Initials,
RowCounter & '_GEO' AS FieldName,
GEO AS FieldValue
RESIDENT TempLoad;

DROP TABLE TempLoad;

FinalTable:
Generic LOAD
Initials,
FieldName,
FieldValue
RESIDENT StackTable;

DROP TABLE StackTable;

Re: How to go from several rows per ID to one row per ID with several columns in load script?

johnjust67 — Sat, 22 Nov 2025 22:28:55 GMT

You can handle this by using a pivot-style transformation in the Qlik Sense load script with a Generic Load. It restructures repeated rows into separate fields automatically. I used a similar approach recently while organizing some test data in Y888, and it worked smoothly here too.

Re: How to go from several rows per ID to one row per ID with several columns in load script?

blipblopdk — Mon, 24 Nov 2025 05:54:04 GMT

Hi Daniel,

Ok, there was one little flaw in the script. I just mention it in case others would copy the script.

In the following part regarding table D, at the end the 'F' should be replaced by 'D':

D :

LOAD UDD

RESIDENT A

Where Initials='$(vValue)' ;

LET vUDD = peek('UDD', j-1, 'F');

Re: How to go from several rows per ID to one row per ID with several columns in load script?

blipblopdk — Mon, 24 Nov 2025 06:46:45 GMT

Hi Nagaraju_KCS,

Thank you so much for a shorter code version that solves the issue. I do however get an error at "ORDER BY" in my load script at table "TempLoad" but I have made a workaround.

TempLoad:
Load
Initials,
UDD,
GEO,
If(Initials = Peek('Initials'), Peek('RowCounter') + 1, 1) AS RowCounter
ORDER BY Initials;

Preview

BR Kim