Qlik Sense Table Transpose

[rajany089]

I have a table with the following columns:

1. Request Number: This column contains repeated values.

2. Question Text: The values in this column should become the column headers of the table.

3. Question Value: The values in this column correspond to the Question Text and should be the contents of the new columns.

4. Opened At: This column also contains repeated values, and I want it to appear as a single value for each request.

5. ID: This column contains repeated values, and I want it to appear as a single value for each request.

I need assistance in transforming this data in Qlik Sense so that each unique request number has a single row with the Question Text values as columns and the corresponding Question Values as their contents. Additionally, the "Opened At" and "ID" columns should appear as single values for each request. Later i want to use this table as input. Thanks In advance 

Attached is the example of Current and Expected 

[QFabian]

HI @rajany089 , here you have an option, without crosstable

Data:

Load * Inline [

A, B, C, D, E, F, G, H

1, ID,123,2024-06-01,1234

1, Name,Tom,2024-06-01,1234

1, Address,US,2024-06-01,1234

1, Age,21,2024-06-01,1234

2, ID,321,2024-09-01,4367

2, Name,Nick,2024-09-01,4367

2, Address,UK,2024-09-01,4367

2, Age, 31, 2024-09-01, 4367

];

Expected:

Load

A as A,

C as ID

Resident Data

Where

B = 'ID';

left join

Load

A as A,

C as Name

Resident Data

Where

B = 'Name';

left join

Load

A as A,

C as Address

Resident Data

Where

B = 'Address';

left join

Load

A as A,

C as Age

Resident Data

Where

B = 'Age';

left join

Load distinct

A as A,

D as D

Resident Data; 

left join

Load distinct

A as A,

E as E

Resident Data;

drop table Data;

exit script; /*

[rajany089]

Hi @QFabian  This was just an example where we have 3-4 columns , in real i have around 15-20 columns , is there any easier way to solve this. Thank you for your help on this 

[QFabian]

HI @rajany089 , here you have an option, without crosstable

Data:

Load * Inline [

A, B, C, D, E, F, G, H

1, ID,123,2024-06-01,1234

1, Name,Tom,2024-06-01,1234

1, Address,US,2024-06-01,1234

1, Age,21,2024-06-01,1234

2, ID,321,2024-09-01,4367

2, Name,Nick,2024-09-01,4367

2, Address,UK,2024-09-01,4367

2, Age, 31, 2024-09-01, 4367

];

Expected:

Load

A as A,

C as ID

Resident Data

Where

B = 'ID';

left join

Load

A as A,

C as Name

Resident Data

Where

B = 'Name';

left join

Load

A as A,

C as Address

Resident Data

Where

B = 'Address';

left join

Load

A as A,

C as Age

Resident Data

Where

B = 'Age';

left join

Load distinct

A as A,

D as D

Resident Data; 

left join

Load distinct

A as A,

E as E

Resident Data;

drop table Data;

exit script; /*