topic Re: How to split one row into multiple rows based on column name? in QlikView

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

songzixian2016 — Mon, 17 Sep 2018 15:44:21 GMT

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

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

captain89 — Mon, 17 Sep 2018 17:01:15 GMT

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.....

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

songzixian2016 — Mon, 17 Sep 2018 17:12:59 GMT

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

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

vishsaggi — Mon, 17 Sep 2018 17:15:49 GMT

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;

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

vishsaggi — Mon, 17 Sep 2018 18:00:47 GMT

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

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

songzixian2016 — Mon, 17 Sep 2018 18:17:25 GMT

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.

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

vishsaggi — Mon, 17 Sep 2018 20:36:19 GMT

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;

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

songzixian2016 — Mon, 17 Sep 2018 20:57:33 GMT

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

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

vishsaggi — Mon, 17 Sep 2018 21:30:07 GMT

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