topic Data Load - Rows to Columns in App Development

Data Load - Rows to Columns

Jim_O — Tue, 05 Nov 2024 09:17:36 GMT

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.

Re: Data Load - Rows to Columns

Kushal_Chawda — Tue, 05 Nov 2024 10:01:28 GMT

@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

Re: Data Load - Rows to Columns

Jim_O — Tue, 05 Nov 2024 11:16:29 GMT

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

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

Re: Data Load - Rows to Columns

Kushal_Chawda — Tue, 05 Nov 2024 11:34:02 GMT

@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