How to split one row into multiple rows based on column name?

[songzixian2016]

Hi all,

I have a sample data like below. I need to put it in a data structure where "start date" and "end date" is displayed per phase per row

ID	A start date	A end date	B start date	B end date	C start date	C end date
1	4/21/2014	4/30/2015	5/1/2015	4/25/2018	4/26/2018	4/25/2021

My expected result is:

ID	Phase	Start Date	End Date
1	A	4/21/2014	4/30/2015
1	B	5/1/2015	4/25/2018
1	C	4/26/2018	4/25/2021

How can I use loading script to create my expected resulting table?

Thank you

[captain89]

Hi,

yes you can with concatenate:

final_table:

load

ID,

'A' as Phase,

[A start date] as [Start Date],

[A end date] as [End Date]

from your_table;

concatenate

load

ID,

'B' as Phase,

[B start date] as [Start Date],

[B end date] as [End Date]

from your_table;

concatenate

load

ID,

'C' as Phase,

[C start date] as [Start Date],

[C end date] as [End Date]

from your_table;

and maybe you can make a cycle load if you have D, E, F, G.....

[songzixian2016]

Thank you so much Matteo! Tried to use CROSSTABLE for it and failed, Didn't think about Concatenate before. Your response is very helpful!

[vishsaggi · Accepted solution]

Not sure if this is a good way of doing it but may be you can try below script.

CrxTable:

CrossTable(Phase, Dates)

LOAD ID,

     [A start date],

     [A end date],

     [B start date],

     [B end date],

     [C start date],

     [C end date]

FROM

[https://community.qlik.com/thread/314203]

(html, utf8, embedded labels, table is @1);

NoConcatenate

FinalTab:

LOAD *,

     Left(Phase,1) AS Cat,

     Mid(Phase, 3, Len(Phase)) AS DateValues

Resident CrxTable;

Drop Table CrxTable;

NoConcatenate

FinalTab1:

LOAD ID, Cat as Phase, Dates as StartDate

Resident FinalTab

Where DateValues = 'start date';

JOIN(FinalTab1)

LOAD ID, Cat as Phase, Dates as EndDate

Resident FinalTab

Where DateValues = 'end date';

Drop Table FinalTab;

[vishsaggi]

Did you try with my script? Just checking if you were successful.

[songzixian2016]

Hi Vishwarath, I tried it. It works if my field name is exactly same as sample data above.

In reality, my data is "XXX start date" or "XXXX start date" (the number of characters before "start date" or "end date" is inconsistant) instead of "single Character + start date" so Left(Phase, 3) AS Cat and Mid(Phase, 3, Len(Phase)) AS DateValues does not work in my situation. I believe some script to parse out category name based on space can solve this but do not know how to do that. But generally the approach is helpful. Thank you.

[vishsaggi]

My understanding was like if you have more phases you need to add the script again and again using Concatenate. SO try below in your script where you have left and mid functions like

NoConcatenate

FinalTab:

LOAD *,

     Left(Phase, Index(Phase, ' ')) AS Cat,

     Mid(Phase, Index(Phase, ' ')+1, Len(Phase)) AS DateValues

Resident CrxTable;

[songzixian2016]

It works well. Compare to "Concatenate" approach it is indeed less code. Thanks again.

[vishsaggi]

No problem, just trying to suggest one time script solution.