Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Valerie
New Contributor II

transpose row to column with it's own value in script

I would like to transpose data from row to column with it's own data value.

Here is the original data sample:

IDCreated atRemarks
10011/5/2019No Answer
10013/5/2019Cancelled
100130/4/2019Called
10022/5/2019No Answer
10021/5/2019Follow Up

 

I would like the result to be as below:

IDRemarks 1Remarks 2Remarks 3
1001CalledNo AnswerCancelled
1002Follow UpNo Answer 

 

Group by the ID, remarks should be order by created at date or time.

 

Labels (3)
3 Replies
Partner
Partner

Re: transpose row to column with it's own value in script

Firstly  you need to generate a remark fiel pointing out which remark each row is.

IDCreated atRemarkNoRemarks
10011/5/2019Remark2No Answer
10013/5/2019Remark3Cancelled
100130/4/2019Remark1Called
10022/5/2019Remark2No Answer
10021/5/2019Remark1Follow Up

 

When you have this information per row as in the table above, then you can start "transposing" it using generic load followed by a join of the generic load output. Take a look at @Henric_Cronström  blog The Generic Load

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
asinha1991
Contributor III

Re: transpose row to column with it's own value in script

elaborating on what @Vegar  already said, you can do something like this...first load data in sometable..then do this.

TmpTable:

Load *,'Remark '&autonumber(rowno(),ID) as RemarkName;

Load ID,Remarks Resident Sometable order by created at;

Drop sometable;

FinalTable;

Generic Load ID, RemarkName,Remarks  Resident TmpTable;

Drop TmpTable;

mahaveerbiraj
Contributor

Re: transpose row to column with it's own value in script

Hello,

please try with below logic,

temp:

load *

Inline [
ID, Created at, Remarks,
1001, 1/5/2019, No Answer
1001, 3/5/2019, Cancelled
1001, 30/4/2019, Called
1002, 2/5/2019, No Answer
1002, 1/5/2019, Follow Up
];

left join(temp)
temp2:
Load
*
Inline
[ Remarks, Remarks_final
No Answer,Remarks 2
Cancelled,Remarks 3
Called ,Remarks 1
Follow Up ,Remarks 1
];

final:
Generic
load
ID,
Remarks_final,
Remarks
Resident temp;

Drop Table temp;