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

[blipblopdk]

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

[Daniel_Castella · Accepted solution]

Hi @blipblopdk 

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

A:

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

];

B: 

LOAD 

DISTINCT Initials 

RESIDENT A;

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

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

    C: 

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

    G: 

            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

I:

LOAD 

Initials,

Count(UDD) as Loop2

RESIDENT A

GROUP BY Initials;

J:

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

E:

NoConcatenate

LOAD *

RESIDENT B

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

DROP TABLE A, B, I, J;

Kind Regards

Daniel

[blipblopdk]

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

[Nagaraju_KCS]

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;

[johnjust67]

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.