Spilt column in two different fields

[Ayden]

hello All, is it possible in Qlik to spilt a field to create additional fields. for example i have a column like this.

CO,FEB-2022.

Question can i column to be 

1st Column would be CO.

2nd Column would be FEB

3rd Column would 2022.

below is a sample script, the end goal is to use a crosstable but first I think I need to spilt up the Columns..

Load

ID,

Shop,

Parts,

Details,
[QC.FEB-2022],
[QC.MAR-2022],
[QC.APR-2022],
[QC.MAY-2022],
[QC.JUN-2022],
[QC.JUL-2022],
[QC.AUG-2022],
[QC.SEP-2022],
[OCT-2022],
[QC.NOV-2022],
[QC.DEC-2022],
[QC.JAN-2023],
[QC.FEB-2023],
[QC.MAR-2023],
[QC.APR-2023],
[MAY-2023],
[QC.JUN-2023],
[QC.JUL-2023],
[QC.AUG-2023],
[QC.SEP-2023],
[OCT-2023],
[QC.NOV-2023],
[QC.DEC-2023],
[QC.JAN-2022]

From XYZ;

[dushyant]

Hello!

Im new to qlik sense...I have used the subfield function but thats only applicable to the rows within a column. Not sure if we can use it to split the columns by its name. Also, if you are trying to use crosstable then there needs to be some data within each column created. In your case when the columns are split and you get a field named QC  then there needs to be some data below it. Hence I'm not sure if crosstable will be applicable in this scenario.

Lets wait for an experienced user to answer your query. Something I shall too get to learn.

[MarcoWedel · Accepted solution]

Hi,

maybe one solution could be:

tabTemp:
CrossTable (ColumnName, ColumnValue, 4)
Load * Inline "
ID,Shop,Parts,Details,[QC.FEB-2022],[QC.MAR-2022],[QC.APR-2022],[QC.MAY-2022],[QC.JUN-2022],[QC.JUL-2022],[QC.AUG-2022],[QC.SEP-2022],[OCT-2022],[QC.NOV-2022],[QC.DEC-2022],[QC.JAN-2023],[QC.FEB-2023],[QC.MAR-2023],[QC.APR-2023],[MAY-2023],[QC.JUN-2023],[QC.JUL-2023],[QC.AUG-2023],[QC.SEP-2023],[OCT-2023],[QC.NOV-2023],[QC.DEC-2023],[QC.JAN-2022]
1,Shop1,Part1,Detail1,1,6,11,16,21,26,31,36,41,46,51,56,61,66,71,76,81,86,91,96,101,106,111,116
2,Shop1,Part2,Detail3,2,7,12,17,22,27,32,37,42,47,52,57,62,67,72,77,82,87,92,97,102,107,112,117
3,Shop2,Part2,Detail2,3,8,13,18,23,28,33,38,43,48,53,58,63,68,73,78,83,88,93,98,103,108,113,118
4,Shop3,Part1,Detail4,4,9,14,19,24,29,34,39,44,49,54,59,64,69,74,79,84,89,94,99,104,109,114,119
5,Shop3,Part3,Detail2,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105,110,115,120";

table1:
Load *,
     Month(MonthYear) as Month,
     Year(MonthYear) as Year;
Load *,
     Left(ColNam,Index(ColNam,'.')-1) as Type,
     Date#(Mid(ColNam,Index('.'&ColNam,'.',-1)),'MMM-YYYY') as MonthYear;
Load *,
     PurgeChar(ColumnName,'[]') as ColNam
Resident tabTemp;

Drop Table tabTemp;

hope this helps

Marco

[Ayden]

Thanks for  pointing me in the right direction