Script to partially transform one column and bring values into corresponding columns

[Mini_Elaine]

Hi there,

I am not sure whether this is a excel question or can be done by Qliksense. I have database like below:

ref number	Task	date
CCR1234	TASK1	45262
CCR1234	TASK2	45424
CCR1234	TASK3	44813
CCR1233	TASK1	44750
CCR1233	TASK2	41130
CCR1233	TASK3	44750

I would like to partially transform 'task' column value into new columns and bring 'date' column values into the corresponding column:

Ref number	Task1	Task2	Task3
CCR1234	45262	45424	44813
CCR1233	44750	41130	44750

Can Qliksense do this? What script to write?

Many thanks!!

[JHuis · Accepted solution]

Hi,

good luck:

DEF:

Load * inline [

refnumber , Task , date

CCR1234 , TASK1 , 45262

CCR1234 , TASK2 , 45424

CCR1234 , TASK3 , 44813

CCR1233 , TASK1 , 44750

CCR1233 , TASK2 , 41130

CCR1233 , TASK3 , 44750

];

TDEF:

NoConcatenate Load

*,

refnumber&'-'&Task as Key

Resident DEF;

Drop table DEF;

FinalTable:

LOAD DISTINCT Task

Resident TDEF;

FOR i = 1 to FieldValueCount('Task')

LET vState = FieldValue('Task', $(i));

Left Join (FinalTable)

LOAD DISTINCT refnumber,

date as [$(vState)]

Resident TDEF

Where Task = '$(vState)';

NEXT

Drop field Task;

Final:

NoConcatenate Load distinct 

*

REsident FinalTable;

DROP Table TDEF,FinalTable;

[Mini_Elaine]

Thank you JHuis!! That works very well!