Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.....
Thank you so much Matteo! Tried to use CROSSTABLE for it and failed, Didn't think about Concatenate before. Your response is very helpful!
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;
Did you try with my script? Just checking if you were successful.
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.
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;
It works well. Compare to "Concatenate" approach it is indeed less code. Thanks again.
No problem, just trying to suggest one time script solution.