Data Load - Rows to Columns

[Jim_O]

Hi guys,

I need to transform some data and then output to CSV.

There are a variable n number of users per ID.

This seems like it should be easy to do in the Qlik Load script, but I'm not finding anything useful...

Thanks in advance.

[Kushal_Chawda]

@Jim_O  try below

Data:

Generic Load ID,

             'User ' & AutoNumber(User)as Row,

             User;

Load * Inline [

ID,User

112,A

112,B

112,C

113,A

113,B];

Final:

Load FieldValue('ID',RecNo()) as ID

AutoGenerate FieldValueCount('ID');

FOR i = NoOfTables()-1 to 0 STEP -1;

LET vTable=TableName($(i));

    IF WildMatch('$(vTable)','Data.*') THEN

    LEFT JOIN ([Final]) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

    ENDIF

NEXT i

[Jim_O]

Thanks for the reply - it's very close to what I need.

I wasn't clear enough around "User", apologies, here's an update.

[Kushal_Chawda · Accepted solution]

@Jim_O  try below

Data:
Load * Inline [
ID,User
112,A
112,B
112,C
113,E
113,F];

rank:
Load ID,
     'User ' & if(ID=Previous(ID),rangesum(keepchar(Peek('User_No'),'0123456789'),1),1) as User_No,
     User
Resident Data
Order by ID,User;

Drop Table Data;

generic_load:
generic Load ID,
             User_No,
             User
Resident rank;

Drop Table rank;


Final:
Load FieldValue('ID',RecNo()) as ID
AutoGenerate FieldValueCount('ID');
 
FOR i = NoOfTables()-1 to 0 STEP -1;
LET vTable=TableName($(i));
    IF WildMatch('$(vTable)','generic_load.*') THEN
    LEFT JOIN ([Final]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
    ENDIF
NEXT i